VBA for simple query

  • Thread starter Thread starter AccessMan
  • Start date Start date
A

AccessMan

I have a form with an unbound combo box into which the user enters a value
and then clicks a button. I would like to use VBA on the button's On Click
event to determine if the entered value matches a record in a table by virtue
of comparison to the values in the table's single field key. In general, I
need to execute an sql query with VBA and determine if the results are null
or not null. Are there any VBA statements/commands available for such
purposes?

Thanks!
 
Simplest would be to use the DLookup function:

If IsNull(DLookup("[FieldName]", "[TableName]", _
"ID = " & Me!MyComboBox)) Then
' The record doesn't exist
Else
' The record exists
End If
 
And one might put DJS's suggested code in the After Update event of the Combo
Box rather than use a separare Command Button ...

Douglas J. Steele said:
Simplest would be to use the DLookup function:

If IsNull(DLookup("[FieldName]", "[TableName]", _
"ID = " & Me!MyComboBox)) Then
' The record doesn't exist
Else
' The record exists
End If



--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


AccessMan said:
I have a form with an unbound combo box into which the user enters a value
and then clicks a button. I would like to use VBA on the button's On
Click
event to determine if the entered value matches a record in a table by
virtue
of comparison to the values in the table's single field key. In general,
I
need to execute an sql query with VBA and determine if the results are
null
or not null. Are there any VBA statements/commands available for such
purposes?

Thanks!
Click to expand...
Click to expand...
 
Or, better yet, use a query on the very table as the Row Source for the Combo
Box and set the CB's "Limit to List" property to True ... your operator can
then pick an item in the CB with the mouse (rather than have to type) and she
won't be _able_ to put a value not in the table into the CB.

Frank said:
And one might put DJS's suggested code in the After Update event of the Combo
Box rather than use a separare Command Button ...

Douglas J. Steele said:
Simplest would be to use the DLookup function:

If IsNull(DLookup("[FieldName]", "[TableName]", _
"ID = " & Me!MyComboBox)) Then
' The record doesn't exist
Else
' The record exists
End If



--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


AccessMan said:
I have a form with an unbound combo box into which the user enters a value
and then clicks a button. I would like to use VBA on the button's On
Click
event to determine if the entered value matches a record in a table by
virtue
of comparison to the values in the table's single field key. In general,
I
need to execute an sql query with VBA and determine if the results are
null
or not null. Are there any VBA statements/commands available for such
purposes?

Thanks!
Click to expand...
Click to expand...
Click to expand...
 
Back
Top