syntax problems

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I am trying to add some code to a button placed in a form, but I'm new to
visual basic programming in Access, and am having troubles with syntax.

Perhaps someone could drop me a snippet of code to get me started??

What I'm trying to do is:

1) when button is pushed, code reads in 3 variables from combo boxes in form

2) using these three variables, read row by row (record by record) through a
4 column (field) table, and select the unique record there the values in the
last 3 columns match the 3 variables

3) retrieve the value of the first column (the record number/primary key)
that identifies the unique row (record)

The ultimate goal is to use this record number to sort data in a subform
that I have ready, but not yet moved into the main form.

regards,
slowuptake
 
You can get started by looking in Access Help for DlookUp() function. You
may also want to have a look at the Nn() function. There is no need to
store the values of the combo boxes in variables since you can access them
directly in an expression. There is no need to search the table row by row.
Let the Dlookup() function find the row and retrieve the value for you.

Sounds like a:

Private Sub cmdYourButton_Click()
Dim lngMyVar as Long

lngMyVar = Nz(DlookUp("Column1Name", "TableWith4Columns", & _
"Colum2Name=" & Combo1.Value & "AND " _
"Colum3Name=" & Combo2.Value & "AND " _
"Colum4Name=" & Combo3.Value ),0)
' Use lngMyVar any way you like below
End Sub

The above assumes that the all of the columns in the table hole numeric
values. You will need to modify the above to handle any columns that are
strings or dates.


Ron W
www.WorksRite.com
 
Back
Top