How can I capture the field values of a record in code?

[cmancuso]

I am trying to both capture values and edit the values of a record in VBA.

I have a query that counts all records where field1="Y" and field2="X". I have a button on the form that allows me to "Sell" on of the iems from the above query.

What I don't know how to do is code, Find the first of these records, copy each field value to a variable (for use elsewhere) then change the value of field1 from "Y" to "N" so that it will not be counted the next time the query runs and thus cannot be oversold.

Again, I want to do all of this in code not with any form values.

Thanks,
Craig

 

[mdmilner]

The method I'd recommend is slightly different from the flow of events you're requesting.

If you already know how to build a SQL query that gets the desired results...
aka,

SELECT * FROM tblName WHERE fields1='Y' AND field2='X'

Open up VBA and create a recordset object. The recordset will only including fields with the parameter given. You can then open the recordset and "walk" through it to perform actions on the fields within each record. You can also pass the values into an array, should you still need to do so.

Personally, I'm cautious - I think there's a lot more to this question...and the question drastically changes the approach to get the right answer.

Tell me if I'm wrong, but it sounds like you're buying and selling a commercial instrument such as stock. The stock might be purchased in a variety of quantities and you're selling a specified amount which may require you to use a FIFO technique to sell the oldest stock first? You'll probably need to calculate a basis and then record that data somewhere for accounting purposes?

The list goes on...Assuming I'm guessing correctly, have you thought about some of the commercial software available that already does similar functions for you? Developing such an application with all the nuances and legal changes yearly would be a large undertaking compared to the $20-$200 you might spend to simply buy it.

No, I'm not cutting out on you and refusing to offer help, just looking for clarification of the exact problem.

Mike

 

[cmancuso]

Mike,

What I am trying to do has nothing to do with stocks. I have specific products coming in. At the time they are added to the db, a field (Available) is set to yes. Using the count query stated above, I am showing all products with a count greater than zero. The code i would like to implement would fin the first instance of the selected product and change the Available field to "No". I also need to capture other elements of the record in a variable to pass to a seperate routine.

I don't know how to find and/or address the specific record and elements of the record in a vba module.

Craig

 

[Norie]

Generally to get records from a table in code:

Dim db As Database
Dim rst As Recordset
Dim fld As Field

    Set db = CurrentDb
    Set rst = db.Openrecordset("MyTable")

    rst.MoveFirst
    While Not (rst.EOF)
    
        With rst

' in this section you could do various things
' like deleting/adding and updating records
' check Help for recordsets

            For Each fld In .Fields
                Debug.Print fld.Name, fld.Value
         
            Next
            rst.MoveNext
        End With
    Wend
    
    Set rst = Nothing
    Set db = Nothing

Not a solution for you but it might get you started

 

[cmancuso]

Norie,

I appreciate the help. I know how to open the recordset and find the record that meets the criteria I need. What I don't know is how to address the fields to cahnge one and get values from the rest.

Craig

 

[mdmilner]

This is a more specific example. The field strSQL is basically being set to the SQL string that you could also use in a Query (QBE wizard built into Access). It filters out all non-applicable records. You could add an additional parameter to the SQL doing something like adding
"ORDER BY fieldname" which would sort the results of the query.

My opinion is, you may need to sort by the date the items were entered into your inventory, assuming you're using FIFO/first in first out.

Function GetRecords()
Dim dbs As Database 
Dim rs As Recordset 
Dim strSQL As String
    
    Set dbs = CurrentDb 
    strSQL = "SELECT * FROM tblName WHERE fields1='Y' AND field2='X'"
    Set rs = dbs.OpenRecordset(strSQL, dbOpenDynaset) 
    
   With rs
      Do Until rs.EOF

            ' code here to work with records
            ' examples noted below
            .Movenext
       Loop
    End With 


    Set rs = Nothing 
    Set db = Nothing 
End Function

Now, here is how you read field contents. Within the above Do...Loop you'd put something like...which assigns the field value in the very first column (column 0 by default) to the variable strVal

  strVal = .Fields(0).Value

Now, here is a much more elegant way to read field values. It's similar to Nories approach.

' Place with reset of Dim statements in above code
Dim astrVal() As String    ' Array of String Values
Dim x, cntField As Integer

' Place below inside the Do...Loop
cntField = .Fields.Count -1   ' gives you total unique fields in table
ReDim astrVal(cntField)       ' sets array to size of number of fields
For x = 0 to cntField
  astrVal(x) = .Fields(x).Value
Next x

I prefer the above over Nories method when writing to arrays since I can use the same variables to set the array position and field position.

Now, here is how you change field values.

.Edit
.Field(x).Value = "whatever_you_wish_to_set"
.Update

The limited answer to your question would then be:

Function GetRecords()
Dim dbs As Database 
Dim rs As Recordset 
Dim strSQL As String
Dim astrVal() As String    ' Array of String Values
Dim x, cntField As Integer

    Set dbs = CurrentDb 
    strSQL = "SELECT * FROM tblName WHERE fields1='Y' AND field2='X'"
    Set rs = dbs.OpenRecordset(strSQL, dbOpenDynaset) 

    With rs
       Do Until rs.EOF
        
          cntField = .Fields.Count -1   ' gives you total unique fields in table
          ReDim astrVal(cntField)       ' sets array to size of number of fields
          For x = 0 to cntField
            astrVal(x) = .Fields(x).Value
          Next x
          
          ' variable z is not declared above.  It really isn't intended to be
          ' a variable it's just for the example
          ' You could also use   .Fields("field1").Value
          ' Or, optionally !field1  as a reference to the name.
          ' I prefer the format below for speed

          .Edit
          .Fields(z).Value = "N"         ' z is field position of field1
          .Update
          .Movenext

    
    ' This is the point where you need to do something with the array
    ' You've read all fields in the record, reset the field1 flag to "N" above
    ' You could send the values to another routine right here
    ' Would look something like
    ' Call FunctionName(astrVal(1), astrVal(2), astrVal(3))
    ' Which sends the values of just those three field
 
         Loop
    End With

    Set rs = Nothing 
    Set db = Nothing 
End Function

I realize this is a lot to absorb, but I tried to include it in steps.

Mike