syntax problems

S

slowuptake

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
 
R

Ron Weiner

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
 
S

slowuptake

Ron,

Thanks for that, considerably more elegant than what I was attempting.
Here is how I tried translate your instructions:

IngMyVar = Nz(DlookUp("idnTimeCardID", "tblTimeCardID", & _
"idnEmployeeID=" & txtEmployeeID.Value & "AND" _
"chrMonth=" & cboMonth.Text & "AND" _
"sngYear" & cboYear.Value ),0)

I must have something slightly wrong however, as this causes compiler to
respond with a syntax error as soon as I type in the expression.

regards,
slowuptake
 
R

Ron Weiner

In that you did not see fit to tell us what the error was, here is a Wild
A-- Guess

IngMyVar = Nz(DlookUp("idnTimeCardID", "tblTimeCardID", & _
"idnEmployeeID=" & txtEmployeeID.Value & "AND" _
"chrMonth='" & cboMonth.Value & "'AND" _
"sngYear" & cboYear.Value ),0)

Where I am guessing that month is a string. Also do not attempt to use the
Text property of a control unless you have set the focus to the control.

To Fully determine what is going wrong you should use Debug.Print.

Debug.Print txtEmployeeID.Value
Debug.Print cboMonth.Value
Debug.Print cboYear.Value

what prints?
 
D

Douglas J. Steele

The & in the first line isn't required but there are some additional ones
required on the other lines, you need spaces around the words AND and you're
missing the equal sign after sngYear. It should be:

IngMyVar = Nz(DlookUp("idnTimeCardID", "tblTimeCardID", _
"idnEmployeeID=" & txtEmployeeID.Value & " AND " & _
"chrMonth=" & cboMonth.Text & " AND " & _
"sngYear=" & cboYear.Value ),0)

Of course, as Ron implied, this will only work if all three values are
numeric. If they're text, you need to include quote marks around the values.
For instance, if cboMonth.Text does return the name of the month, rather
than the number, you'd use:

IngMyVar = Nz(DlookUp("idnTimeCardID", "tblTimeCardID", _
"idnEmployeeID=" & txtEmployeeID.Value & " AND " & _
"chrMonth=" & Chr$(34) & cboMonth.Text & Chr$(34) & _
" AND sngYear=" & cboYear.Value ),0)
 
S

slowuptake

Ron,

Ah a miniscule amount of knowledge is a wonderful thing!

The original problem was that the syntax was wrong enough that compiler
wouldn't start. It appeared to be with the use of "&". I also had forgotten
one = sign. Below is entire routine as it now stands:



Private Sub cmdFindTimeSheet_Click()
On Error GoTo Err_cmdFindTimeSheet_Click

Dim IngMyVar As Long

IngMyVar = Nz(DLookup("idnTimeCardID", "tblTimeCardID", _
"idnEmployeeID=" & txtEmployeeID.Value & "AND" _
& "chrMonth=" & cboMonth.Text & "AND" _
& "sngYear=" & cboYear.Value), 0)

Exit_cmdFindTimeSheet_Click:
Exit Sub

Err_cmdFindTimeSheet_Click:
MsgBox Err.Description
Resume Exit_cmdFindTimeSheet_Click

End Sub



Now it will compile and run, but it kicks out to "Err_cmdFindTimeSheet_Click"
when it attempts to evaluate IngMyVar. The error message is: "You can't
reference a property or method for a control unless the control has the
focus".

I tried changing "cboMonth.Text" to "cboMonth.String". This generates the
error message "method or data member not found" and opens the debut window
and highlights "string" in blue.

I tried changing "cboMonth.Text" to "cboMonth.Value". This generates the
error message: "Syntax error (missing operator) in
'idnEmployeeID=2ANDchrMonth=JulyANDsngYear=205' "

I checked the original table design view, idnEmployeeID and sngYear are
defined as numbers, chrMonth as text.

I was not successful in getting "debug.print" to work. I don't know why. I
tried placing both before and after IngMyVar, but nothing prints, although I
can print from the debug window otherwise ... sorry, I didn't choose the
loginname for nothing!

It is now 7PM here, so I am off for a Friday pint, but will get back at this
tomorrow. Thanks for the lessons.

regards,
slowuptake
 
D

Douglas J. Steele

As I already pointed out, you need blanks before and after AND.

IngMyVar = Nz(DlookUp("idnTimeCardID", "tblTimeCardID", _
"idnEmployeeID=" & txtEmployeeID & " AND " & _
"chrMonth=" & Chr$(34) & cboMonth & Chr$(34) & _
" AND sngYear=" & cboYear),0)
 
S

slowuptake

I used your second variant below, as it is clear that cboMonth is returning
the name of the month.

When I step through the code, the compiler kicks out to
Err_cmdFindTimeSheet, and gives the message:

"You can't reference a property or method of control unless the control has
the focus".

I presume this means I need to use something like the SetFocus command, but
I'm not sure what needs the focus.

regards,
slowuptake
 
D

Douglas J. Steele

Yeah, that's what I get for just copying your original code!

It's complaining about your use of .Value.

Fortunately, the other version I posted (which omits both the .Value and
..Text properties) will work, since in all cases the default property is the
one you want.

IngMyVar = Nz(DlookUp("idnTimeCardID", "tblTimeCardID", _
"idnEmployeeID=" & txtEmployeeID & " AND " & _
"chrMonth=" & Chr$(34) & cboMonth & Chr$(34) & _
" AND sngYear=" & cboYear),0)
 
S

slowuptake

You're correct that I can't be trusted. I also managed to spell the name of
the table incorrectly. But that repaired, your code below now works perfectly.

Thanks to you and Ron for the lessons.

regards,
slowuptake
 
Top