event procedure

[Guest]

In the db I built I have a form that searches for a value in a specific
column in the db. I have used the expression builder to link the button to
the field in the db. The problem is that if the value is not found in the
table it goes to a new record. I need an error message to come up but I do
not know where the code would be since the procedure was built in expression
builder and not a module or form code in vb.

Any help will be greatly appreciated.

 

[AlCamp]

Cindy,
You wrote...

I have used the expression builder to link the button to the field in the
db.

It would have been helpful if you had included that code so we could see
what you're trying to do.

I'm guessing that you're asking the user for a value for a specific field,
then finding the record that has that value.

Try doing a Dlookup on that value, and if the Dlookup returns Null, then
post a message box, and don't do the Find.

hth
Al Camp

 

[Guest]

I enter a value in the search text box on the form and click on the button
which is linked to this code -
[Tbl_CCD]![ORB_ID]=[Forms]![frmSearch]![Search] - If the value is in the
table a form will come up with the record information if the value is not in
the form a blank form comes up for a new record to be entered. I just want
an error message if the value is not in the table. I have 3 different search
options in the search form so I do not want new records entered that may
already be in the table.

 

[AlCamp]

Cindy,
OK... that just what I thought...

I'm guessing that you're asking the user for a value for a specific field,
then finding the record that has that value.

Did you try my suggestion?

Try doing a Dlookup on that value, and if the Dlookup returns Null, then
post a message box, and don't do the Find.

You'll need to do that for each of your 3 individual search fields.

hth
Al Camp

I enter a value in the search text box on the form and click on the button
which is linked to this code -
[Tbl_CCD]![ORB_ID]=[Forms]![frmSearch]![Search] - If the value is in the
table a form will come up with the record information if the value is not
in
the form a blank form comes up for a new record to be entered. I just
want
an error message if the value is not in the table. I have 3 different
search
options in the search form so I do not want new records entered that may
already be in the table.

Cindy,
You wrote...

It would have been helpful if you had included that code so we could see
what you're trying to do.

I'm guessing that you're asking the user for a value for a specific
field,
then finding the record that has that value.

Try doing a Dlookup on that value, and if the Dlookup returns Null, then
post a message box, and don't do the Find.

hth
Al Camp

 

[Guest]

I tried the DLookup set in the ControlSource for the text box used to enter
the value to search for. When I tried to test this I could not enter the
value in the text box. Have I done something wrong. Is the value entered
another way?

Cindy,
OK... that just what I thought...

I'm guessing that you're asking the user for a value for a specific field,
then finding the record that has that value.

Did you try my suggestion?

Try doing a Dlookup on that value, and if the Dlookup returns Null, then
post a message box, and don't do the Find.

You'll need to do that for each of your 3 individual search fields.

hth
Al Camp

I enter a value in the search text box on the form and click on the button
which is linked to this code -
[Tbl_CCD]![ORB_ID]=[Forms]![frmSearch]![Search] - If the value is in the
table a form will come up with the record information if the value is not
in
the form a blank form comes up for a new record to be entered. I just
want
an error message if the value is not in the table. I have 3 different
search
options in the search form so I do not want new records entered that may
already be in the table.

Cindy,
You wrote...
I have used the expression builder to link the button to the field in
the
db.

It would have been helpful if you had included that code so we could see
what you're trying to do.

I'm guessing that you're asking the user for a value for a specific
field,
then finding the record that has that value.

Try doing a Dlookup on that value, and if the Dlookup returns Null, then
post a message box, and don't do the Find.

hth
Al Camp

In the db I built I have a form that searches for a value in a specific
column in the db. I have used the expression builder to link the
button
to
the field in the db. The problem is that if the value is not found in
the
table it goes to a new record. I need an error message to come up but
I
do
not know where the code would be since the procedure was built in
expression
builder and not a module or form code in vb.

Any help will be greatly appreciated.

 

[AlCamp]

Cindy,
Let's just work with one search value, and apply the same solution
process to all three...
*I'll use example names, you use your own.*
Here's the steps you'll need to take...
1. Enter a CustomerID in your search textbox called FindCustomerID.
2. Click your FindCustomerID button.
3. Determine if such a CustomerID exists
4. If it does, run code to find it.
5. If NOT, alert the user and quit the sub.

Leave your text box the way it was originally, so you can enter a value
to search for.
You said you had a button that you click when you want to try to find
that value.
Using the OnClick event of that button...

Note: Watch out for Email text on this note that "wraps" to another
line...

Private Sub cmdFindCustomerID_Click()
If IsNull(DLookup("[CustomerID]","tblCustomers","[CustomerID] = " &
[FindCustomerID]) Then
MsgBox "No such Customer ID", vbOKOnly, "CustomerID not found"
Exit Sub
Else
'Put your original Finding code here
End If
End Sub

Use the same process for the other two Find fields.

If you still have problems, please tell me exactly what process you used,
and any code involved.
hth
Al Camp

 

[Guest]

Thanks for all you help. I got the DLookup part working
but where you said to put my code for the search I had
built that in expression builder. Here is what I have so
far:

Private Sub cmdSearchAGYID_Click()
If IsNull(DLookup("[AGY_ID]", "Tbl_CCA", "[AGY_ID] = " &
[Search])) Then
MsgBox "No such AGYID", vbOKOnly, "AGYID not
found"
Exit Sub
Else
[Tbl_CCA]![AGY_ID] = [Forms]![frmSearch]![Search]
End If

End Sub

The line of code after Else is what I took from the macro
expression field the error says it cannot find that
field. Is there specific VB code for the search instead
of the way I did it?

Thanks

-----Original Message-----
Cindy,
Let's just work with one search value, and apply the same solution
process to all three...
*I'll use example names, you use your own.*
Here's the steps you'll need to take...
1. Enter a CustomerID in your search textbox called FindCustomerID.
2. Click your FindCustomerID button.
3. Determine if such a CustomerID exists
4. If it does, run code to find it.
5. If NOT, alert the user and quit the sub.

Leave your text box the way it was originally, so you can enter a value
to search for.
You said you had a button that you click when you want to try to find
that value.
Using the OnClick event of that button...

Note: Watch out for Email text on this note that "wraps" to another
line...

Private Sub cmdFindCustomerID_Click()
If IsNull(DLookup

("[CustomerID]","tblCustomers","[CustomerID] = " &

[FindCustomerID]) Then
MsgBox "No such Customer ID",

vbOKOnly, "CustomerID not found"

 

[AlCamp]

OK, we're good so far... now we just need to do a FindRecord if the ID
exists. Your cut & paste ([Tbl_CCA]![AGY_ID] =
[Forms]![frmSearch]![Search]) from the "macro builder" won't work. Putting
a bit of sweat equity into learning to use event procedures and code is well
worth the effort!

I take it your text control with the entered search value is called
[Search], and [Search] is on the same form, and the field you're searching
on is [AGY_ID].

Try this code... (again... use your own names)

Private Sub cmdSearchAGYID_Click()
If IsNull(DLookup("[AGY_ID]", "Tbl_CCA", "[AGY_ID] = " & [Search])) Then
MsgBox "No such AGYID", vbOKOnly, "AGYID not found"
Exit Sub
Else
DoCmd.GoToControl "AGY_ID"
DoCmd.FindRecord [Search]
End If
End Sub

If AGY_ID is unique, this method will find it, if there are multiple records
this method will return the First record that meets the criteria.

So... just create a button for each of the other 2 fields you want to search
on, and just tweak the same basic code to work accordingly, and that should
do it.
hth
Al Camp

Thanks for all you help. I got the DLookup part working
but where you said to put my code for the search I had
built that in expression builder. Here is what I have so
far:

Private Sub cmdSearchAGYID_Click()
If IsNull(DLookup("[AGY_ID]", "Tbl_CCA", "[AGY_ID] = " &
[Search])) Then
MsgBox "No such AGYID", vbOKOnly, "AGYID not
found"
Exit Sub
Else
[Tbl_CCA]![AGY_ID] = [Forms]![frmSearch]![Search]
End If

End Sub

The line of code after Else is what I took from the macro
expression field the error says it cannot find that
field. Is there specific VB code for the search instead
of the way I did it?

Thanks

-----Original Message-----
Cindy,
Let's just work with one search value, and apply the same solution
process to all three...
*I'll use example names, you use your own.*
Here's the steps you'll need to take...
1. Enter a CustomerID in your search textbox called FindCustomerID.
2. Click your FindCustomerID button.
3. Determine if such a CustomerID exists
4. If it does, run code to find it.
5. If NOT, alert the user and quit the sub.

Leave your text box the way it was originally, so you can enter a value
to search for.
You said you had a button that you click when you want to try to find
that value.
Using the OnClick event of that button...

Note: Watch out for Email text on this note that "wraps" to another
line...

Private Sub cmdFindCustomerID_Click()
If IsNull(DLookup

("[CustomerID]","tblCustomers","[CustomerID] = " &

[FindCustomerID]) Then
MsgBox "No such Customer ID",

vbOKOnly, "CustomerID not found"

Exit Sub
Else
'Put your original Finding code here
End If
End Sub

Use the same process for the other two Find fields.

If you still have problems, please tell me exactly what process you used,
and any code involved.
hth
Al Camp



(e-mail address removed)...


.

 

[Guest]

Al thanks for all your help. New issue. The search is not working.
Originally I had the button tied to a macro that would open the form that
contained the record information. When I enter the Id and click the button
it says there is no field name in the current record. How do I direct the
button to the table and the associated form.

Thanks,

Cindy

OK, we're good so far... now we just need to do a FindRecord if the ID
exists. Your cut & paste ([Tbl_CCA]![AGY_ID] =
[Forms]![frmSearch]![Search]) from the "macro builder" won't work. Putting
a bit of sweat equity into learning to use event procedures and code is well
worth the effort!

I take it your text control with the entered search value is called
[Search], and [Search] is on the same form, and the field you're searching
on is [AGY_ID].

Try this code... (again... use your own names)

Private Sub cmdSearchAGYID_Click()
If IsNull(DLookup("[AGY_ID]", "Tbl_CCA", "[AGY_ID] = " & [Search])) Then
MsgBox "No such AGYID", vbOKOnly, "AGYID not found"
Exit Sub
Else
DoCmd.GoToControl "AGY_ID"
DoCmd.FindRecord [Search]
End If
End Sub

If AGY_ID is unique, this method will find it, if there are multiple records
this method will return the First record that meets the criteria.

So... just create a button for each of the other 2 fields you want to search
on, and just tweak the same basic code to work accordingly, and that should
do it.
hth
Al Camp

Thanks for all you help. I got the DLookup part working
but where you said to put my code for the search I had
built that in expression builder. Here is what I have so
far:

Private Sub cmdSearchAGYID_Click()
If IsNull(DLookup("[AGY_ID]", "Tbl_CCA", "[AGY_ID] = " &
[Search])) Then
MsgBox "No such AGYID", vbOKOnly, "AGYID not
found"
Exit Sub
Else
[Tbl_CCA]![AGY_ID] = [Forms]![frmSearch]![Search]
End If

End Sub

The line of code after Else is what I took from the macro
expression field the error says it cannot find that
field. Is there specific VB code for the search instead
of the way I did it?

Thanks

-----Original Message-----
Cindy,
Let's just work with one search value, and apply the same solution
process to all three...
*I'll use example names, you use your own.*
Here's the steps you'll need to take...
1. Enter a CustomerID in your search textbox called FindCustomerID.
2. Click your FindCustomerID button.
3. Determine if such a CustomerID exists
4. If it does, run code to find it.
5. If NOT, alert the user and quit the sub.

Leave your text box the way it was originally, so you can enter a value
to search for.
You said you had a button that you click when you want to try to find
that value.
Using the OnClick event of that button...

Note: Watch out for Email text on this note that "wraps" to another
line...

Private Sub cmdFindCustomerID_Click()
If IsNull(DLookup

("[CustomerID]","tblCustomers","[CustomerID] = " &

[FindCustomerID]) Then
MsgBox "No such Customer ID",

vbOKOnly, "CustomerID not found"

Exit Sub
Else
'Put your original Finding code here
End If
End Sub

Use the same process for the other two Find fields.

If you still have problems, please tell me exactly what process you used,
and any code involved.
hth
Al Camp



(e-mail address removed)...
I tried the DLookup set in the ControlSource for the text box used to enter
the value to search for. When I tried to test this I could not enter the
value in the text box. Have I done something wrong. Is the value entered
another way?


.