Get value from record based on criteria in VBA

[TheFoxy]

Hey,

I've got 3 tables, and a form for entering new records to table 1. I want to take the number entered for one of the fields on that form away from the number stored in a specific record in table 2. The three tables are linked (1 to 3 by an ID number, and 3 to 2 by a keyfield) so there will always be only one record to be found to alter in table 2

I tried to write this but I kept getting errors, and I haven't the knowledge to extract table data in VBA properly anyway, which isn't helpful.

I've written code to grab the number and ID I want from the form, and I can handle the validation on the number field I need to pull out, (making sure it's more than or equal to the number, and giving an error if it isn't) and I can alter the field with DoCmd.RunSQL, so I just need to:

*Grab the Keyfield from table 3 by looking up the ID field from the form;
*Grab the number field from the record in table 2 which has the same keyfield as the keyfield I pulled out;

I tried doing this with DoCmd.RunSQL, but it's a method not a function (D'oh!). I then tried recordsets but they were quite confusing and overcomplicated, and didn't work anyway.

Help? :S

 

[KenReay]

HI

If the relationship between the tables is one to one, then why not base the form on a query joining the three tables and do the update via bound columns, no need for SQL DoCmd etc etc

Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
Website needs upgrading, but for now -

http://www.kenneth.reay.btinternet.co.uk

UK

 

[SiJP]

Ok too ways to do it.

1) using recordsets.. these are well worth getting to grips with, even if a little confusing at first. Heres a way to loop through all the records in a table, and show a message box of a particular fields value.

Dim rst as DAO.Recordset ' Make sure you reference DAO in your library

Set rst = CurrentDB.OpenRecordset("tblTableName", dbOpenDynaset)

'Move to the first record
rst.Movefirst

While not rst.EOF

 Msgbox rst!fldName
 rst.Movenext

Wend


'Close the recordset, then clear the variable.. just good housekeeping.
rst.Close
set rst = Nothing

2) The second way, is to use DLookups - they are a bit slower in large databases with lots of users, but work well for quick fire methods.

DLookup("FieldName" , "TableName" , "Criteria = n")

Let me know if you need specific help.. you may need to post some of the DB structure.

 

[TheFoxy]

I've tried DLookup, but I get the following error:

"Syntax error (missing operator) in query expression 'Serial = XCV5G'

I don't know why I'm getting this error, since the DLookup above this worked perfectly. (NumberOf is an int, TheSerial is a string).

NumberOf = DLookup("Num", "Items", "Serial = " & TheSerial)

I can't see anything wrong with this, so what is Access objecting to?!?

 

[RoyVidar]

Hi!

[tt]DLookup("Num", "Items", "Serial = '" & TheSerial & "'")[/tt]

- single quote as text delimiters

Roy-Vidar

 

[TheFoxy]

Thanks, it works now!

Never knew I had to use single quotes around text for criteria.