Comparing data in a form with data in a table

[CJG via AccessMonster.com]

Hi!

I've created a database for points of interest - consists of 3 tables -
points, gauging and sampling. The points can be in gauging, sampling,both or
neither. I have a form for users to add new events to the points if
necessary. The form has an option box to to add the point to the gauging
table. If the point already exists in the gauging table, I want a message
box to appear to let them know they cannot add another gauging record.

I've tried a few things, the latest was the eval function, however I keep
recieving errors in that it can't find the my table name....I can't seem to
reference a table ......how do you reference a table in VB?

Or am I completely wrong and there is a better way?

Thanks in advance.
CJG

 

[Guest]

Since I don't know where your code is checking to see if the user wants to
add a guaging record, I can't be specific on where to put the code, but it
should be whereever you test the option box ( btw, there is no such thing as
an option box, there is an option group that contains option buttons, toggle
buttons, or check boxes, so I am not sure what you are doing):

If Me.OptionBox = ? ( whatever value that identifies the user wants to add a
gauging record) Then
If Not IsNull(DLookup("[GaugingKeyFieldNameHere]",
"GuagingTableNameHere", "[GuagingFieldNameHere] = '" & Me.GuagingValueHere &
"'" Then
MsgBox "Value Already In Use"
Else
'Ok to add the record
End If

You will have to change the names for your field, table, and control names

 

[CJG via AccessMonster.com]

Thanks Klatuu,

I'm almost there, but get a compile errorr on my If Not IsNull line:
"Expected list separator or ) ". I've looked and can't figure it what is
wrong.

If (Me.yntobeGauged = 1) Then
if Not IsNull (DLookup("[SamplePoint]","[tbl_Gauging]" ,"[tbl_Gauging]!
[SamplePoint]= '" & "'" & Me.[nSamplePoint] & "'") Then
MsgBox "Sample Point Already Scheduled for Gauging. Please Choose No
or Select Another Sample Point", vbOKOnly, ""
Else
MsgBox "okay to add"
End If
End if

Since I don't know where your code is checking to see if the user wants to
add a guaging record, I can't be specific on where to put the code, but it
should be whereever you test the option box ( btw, there is no such thing as
an option box, there is an option group that contains option buttons, toggle
buttons, or check boxes, so I am not sure what you are doing):

If Me.OptionBox = ? ( whatever value that identifies the user wants to add a
gauging record) Then
If Not IsNull(DLookup("[GaugingKeyFieldNameHere]",
"GuagingTableNameHere", "[GuagingFieldNameHere] = '" & Me.GuagingValueHere &
"'" Then
MsgBox "Value Already In Use"
Else
'Ok to add the record
End If

You will have to change the names for your field, table, and control names

Hi!

[quoted text clipped - 13 lines]

Thanks in advance.
CJG

 

[Guest]

Your problem is here:
[SamplePoint]= '" & "'" & Me.[nSamplePoint] & "'") Then
Should be:
[SamplePoint]= '" & Me.[nSamplePoint] & "'") Then

Also, It is not necessary to identify the table name in the DLookup:
if Not IsNull (DLookup("[SamplePoint]","[tbl_Gauging]" ,"[SamplePoint]=
'" & "'" & Me.[nSamplePoint] & "'") Then

Thanks Klatuu,

I'm almost there, but get a compile errorr on my If Not IsNull line:
"Expected list separator or ) ". I've looked and can't figure it what is
wrong.

If (Me.yntobeGauged = 1) Then
if Not IsNull (DLookup("[SamplePoint]","[tbl_Gauging]" ,"[tbl_Gauging]!
[SamplePoint]= '" & "'" & Me.[nSamplePoint] & "'") Then
MsgBox "Sample Point Already Scheduled for Gauging. Please Choose No
or Select Another Sample Point", vbOKOnly, ""
Else
MsgBox "okay to add"
End If
End if

Since I don't know where your code is checking to see if the user wants to
add a guaging record, I can't be specific on where to put the code, but it
should be whereever you test the option box ( btw, there is no such thing as
an option box, there is an option group that contains option buttons, toggle
buttons, or check boxes, so I am not sure what you are doing):

If Me.OptionBox = ? ( whatever value that identifies the user wants to add a
gauging record) Then
If Not IsNull(DLookup("[GaugingKeyFieldNameHere]",
"GuagingTableNameHere", "[GuagingFieldNameHere] = '" & Me.GuagingValueHere &
"'" Then
MsgBox "Value Already In Use"
Else
'Ok to add the record
End If

You will have to change the names for your field, table, and control names

Hi!

[quoted text clipped - 13 lines]

Thanks in advance.
CJG

 

[CJG via AccessMonster.com]

Many thanks Klatuu!

You miss things after looking at them too long!

Your problem is here:
[SamplePoint]= '" & "'" & Me.[nSamplePoint] & "'") Then
Should be:
[SamplePoint]= '" & Me.[nSamplePoint] & "'") Then

Also, It is not necessary to identify the table name in the DLookup:
if Not IsNull (DLookup("[SamplePoint]","[tbl_Gauging]" ,"[SamplePoint]=
'" & "'" & Me.[nSamplePoint] & "'") Then

Thanks Klatuu,

[quoted text clipped - 34 lines]