Re: syntax problems

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

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)


--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)



"slowuptake" <slowuptake@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:0366A48A-2ABC-474A-A979-C195DFDAFE36@xxxxxxxxxxxxxxxx
>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
>
> "Douglas J. Steele" wrote:
>
>> 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)
>>
>>
>> --
>> Doug Steele, Microsoft Access MVP
>> http://I.Am/DougSteele
>> (no e-mails, please!)
>>
>>
>>
>> "slowuptake" <slowuptake@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
>> news:2FDB8A4C-1286-4512-9C2D-0568AC9F6E44@xxxxxxxxxxxxxxxx
>> > 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
>> >
>> > "Ron Weiner" wrote:
>> >
>> >> 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
>> >> "slowuptake" <slowuptake@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
>> >> news:3AAC43BB-A132-4953-B92D-04FF59F84D85@xxxxxxxxxxxxxxxx
>> >> > 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
>> >>
>> >>
>> >>
>>
>>
>>


.

Relevant Pages

  • Re: Buttonbase and button event firing bugged
    ... Don't get me wrong, reflector is a great tool, but it has good portion of ... in this case original code has just one 'if' and it has no ... > best regards ... >> microsoft employe in a microsoft seminar about security one month ago. ...
    (microsoft.public.dotnet.framework.compactframework)
  • Re: Using Query instead of Table
    ... Whatever control that is on your form that holds the Customer Id, ... Dan Artuso, Access MVP ... but in your original code you use Screen.ActiveControl ... >>> The query that I want to use is based on the table that I used in the ...
    (microsoft.public.access.forms)
  • RE: Requery form
    ... Kate - ... I assume you have a control that the user will use to go to another record ... and then call this code from both the original code and from the ... patient without closing and reopening so that it asks me for the criteria ...
    (microsoft.public.access.forms)
  • RE: Update Checkbox problem
    ... Maryland and their respective owner. ... the form was set to run the update query and close the form. ... then added a new button control to save the record. ... original code to run the update query and close the form and it worked ...
    (microsoft.public.access.queries)
  • Re: Silly question?
    ... C# Beginner wrote: ... look back at the original code: ... Point value that represents the location of the control. ... You then modify that copy to set it's X ...
    (microsoft.public.dotnet.languages.csharp)