Very simple question; if statement

  • Thread starter Thread starter meneedushelpus via AccessMonster.com
  • Start date Start date
M

meneedushelpus via AccessMonster.com

I'm aiming for:

If the value of the field 'location' in a record equals the value in a text
box 'scanlocation' where the value of the 'barcode' field in the record
matches the value in the text box 'ItemID', then do something.

e.g.

If [Equipment]![location] = [scanlocation] Where [Equipment]![barcode] =
[ItemID] Then

*statement*

End If

That doesn't work because of the two statements, is there a way to reword it
to make it work?


Thank you for the help, in advance as I'm stuck in the mud/.
 
If [Equipment]![location] = [scanlocation] And [Equipment]![barcode] =
[ItemID] Then
 
Haha, donut on the block. I can't believe I overlooked that.

I'm having another problem now though, it keeps telling me:

Run-time error '2465':

Microsoft Office Access can't find the field 'I' referred to in your
expression.


It then highlights the if statement?? There is no reference to that, I've put
it exactly as you have written it?? The fields are all correct and it happens
regardless for what is in the text boxes
 
I can't see your code, so I can't be sure, but if Access believes you have a
field named "I", perhaps your "If" statement is missing an "f"...

Regards

Jeff Boyce
Microsoft Office/Access MVP
 
I've seen that effor message before, I think. If it's the one I recall I
think it's a | (pipe character). Unfortunately I can't remember the
circumstances, but as I recall it had something to do with name ambiguity.
Perhaps a control and a field have the same name, or Access interprets
something in the code as a field, but can't find it because it isn't a
field. I agree that seeing the code could help to unravel the mystery.

Jeff Boyce said:
I can't see your code, so I can't be sure, but if Access believes you have
a field named "I", perhaps your "If" statement is missing an "f"...

Regards

Jeff Boyce
Microsoft Office/Access MVP
Click to expand...
 
Cheers for the help nothing seems to work this end, very frustrating/
I've seen that effor message before, I think. If it's the one I recall I
think it's a | (pipe character). Unfortunately I can't remember the
circumstances, but as I recall it had something to do with name ambiguity.
Perhaps a control and a field have the same name, or Access interprets
something in the code as a field, but can't find it because it isn't a
field. I agree that seeing the code could help to unravel the mystery.
Click to expand...


I've tried all sorts of things now. It works until I add the external table
to the if statement. I've also tried referencing to a different table and
query (both of which work fine in other forms) and again it melts.

My code:

Private Sub changelocation_Click()
On Error GoTo Err_changelocation_Click

If [Equipment]![location] = Me.[scanLocation] And [Equipment]![barcode] = Me.
[barcodeID] Then

**

Else

**

End If

[Forms]![Automation Change Location]![scanLocation] = ""
[Forms]![Automation Change Location]![barcodeID] = ""
DoCmd.GoToControl "barcodeID"


Exit_changelocation_Click:
Exit Sub

Err_changelocation_Click:
MsgBox Err.Description
Resume Exit_changelocation_Click

End Sub

Like I say it works perfectly until any external reference is added to the IF
statement :( .
 
Oh I forgot to add that yes you're right, it is the pipe charactor :) .
 
Right. I've tried changing all of the field/tables names and also tried
creating a whole new form. Nothing is working.
I have found one solution which is to create a form for the table and then
stick my interface in the header, with the detail section hidden. It works
but is going to be slow when a lot of records are added.
 
meneedushelpus said:
I'm aiming for:

If the value of the field 'location' in a record equals the value in
a text box 'scanlocation' where the value of the 'barcode' field in
the record matches the value in the text box 'ItemID', then do
something.

e.g.

If [Equipment]![location] = [scanlocation] Where
[Equipment]![barcode] = [ItemID] Then

*statement*

End If

That doesn't work because of the two statements, is there a way to
reword it to make it work?


Thank you for the help, in advance as I'm stuck in the mud/.
Click to expand...

Replace "Where" with "And".
 
Back
Top