Updating Fields in a Recordset

[BarkerD]

I need to move to a specific record in a recordset using the primary key of the table, (2 fields)based on values that I have extracted from a form. I have tried to use the SEEK, FIND and FILTER methods on the recordset object, but none of them seem to allow the use of variables.
Here is an example of the code I am trying to use.

dim intvar1 as integer (item# from form -user entered)
dim intvar2 as integer (customer# from form -user entered)

dim cnn as adodb.connection
dim rcdset as new adodb.recordset

set cnn = currentproject.connection
rcdset.open ("TableName", cnn , adopenkeyset, adlockoptimistic, adcmdtabledirect)

rcdset.??????

I could make this work if I used a constant value instead of the variable.

eg: rcdset.find "[field]=12 and [field2]=30"
rcdset![Balance]="expression"
rcdset.update

but I need to replace the numeric values with the variable names

I hope I haven't been too vague with the description of my problem. I am fairly new at this, and I don't have that much experience with VBA.

Thanks for the help
Duane Barker

 

[BillP]

I usually use an SQL string in cases like this. For example:
SQL = "SELECT * FROM TableName WHERE (FieldName1 = " & intvar1 & ") and (FieldName2 = " & intvar2 & ");" I would then use the SQL as follows set MyRecs = MyDb.OpenRecordset(SQL). I hope this is what you're looking for.

 

[BarkerD]

Thanks for your post, Bill.
I tried what you suggested, but couldn't seem to get it to work.

Here is a sample of the code I have been trying to use...isn't working as of yet

I've got to get this working by Sunday or I am in deep doo-doo

Form_Outsourcing Details

Option Compare Database
Option explicit
Private balvalue as double
Private contractor as integer
private partnumber as integer

Private sub SeekRecord()

dim cn as ADODB.Connection
dim rcdbalance as New ADODB.Recordset

Set cn = CurrentProject.Connection

rcdbalance.open "SELECT * from [Outservice Balances] WHERE ([part#]="&partnumber&") AND ([contractor#]="&contractor&");", _
cn , adopenkeyset , adlockoptimistic , adcmdtabledirect

If not rcdbalance.EOF Then
rcdbalance![balance]=expression
end if
end sub

private sub QtyShipped_Enter()

balvalue=Me![QtyShipped]
contractor = Forms![Outsourcing]![contractor]
partnumber = Me![part#]

End Sub

Private Sub QtyShipped_Exit()

SeekRecord

End Sub

There is other code for this form, but it doesn't apply to this problem

The shipping information entered in this form is added to the balance field in the Balances table for every part/contractor combination

eg: a part can go to multiple contractors

If anyone can help, I would really appreciate it. If I can lick this problem I can really increase the functionality of the database in other areas using the solution.



[This message has been edited by BarkerD (edited 12-02-1999).]

 

[BillP]

I'm sorry to hear you're still having problems with it. I would have done it a bit differently.
Declare SQL as a string.
dim SQL as string
I would then use set to create the dataset.
SQL = "SELECT * from [Outservice Balances] WHERE ([part#]=" & partnumber & ") AND ([contractor#]=" & contracto r & ");"
set MyRecs = MyDb.Openrecordset(SQL).
I'm not sure why it didn't work. I would use debug.print in the code to print out the SQL string, and then copy and paste it as an SQL query to test it. One small thing is that I've found it likes to have a space between the & and the string or variable. Focus on the SQL. Once you have the SQL working you shouldn't have any problems. Good Luck!

 

[jgdavis]

What would the syntax be if the variable partnumber was a text field in the query?

([part#]=" & partnumber & ")

 

[KDg]

I'd put in [field]=" & chr(34) & partnumber & chr(34)
this drops in "" around the text then, otherwise it won't recognize it.