Event procedure

  • Thread starter Thread starter Guest
  • Start date Start date
G

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
 
Cindy,
If your old macro did the Find correctly, then just convert that
sequence of macro instructions into VB code behind your FindID button. You
don't tell me what those macro functions are, so I'll just have to guess...

IF you have a seprate "dialog form" (form Search)** where you are
entering your search criteria then you'll have to use a FIND sequence like
this...
DoCmd.OpenForm "YourIDForm"
DoCmd.GoToControl "AGY_ID"
DoCmd.FindRecord Forms!YourDialogFormName!Search
This is just a "typical" sequence... there's no way that I can know
exactly how you have everything set up. Use it as a guide, and use Help to
understand the Functions, their associated syntax, and then customize this
my process for your particular needs.

**Right now you I think you have a form named Search, and a field on that
form called Search... (duplicates)... that's not good. Your dialog form
should be named frmSearchDialog for clarity, and your field on the form for
the AGY_ID value should be renamed to FindAGY_ID. (and change the code
above accordingly)
hth
Al Camp

Cindy said:
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

AlCamp said:
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
Click to expand...
Click to expand...
 
Al, thank you so much for the time you are takeing to helping me. Below is
the code I currently have:

Private Sub cmdSearchRPPSID_Click()

If IsNull(DLookup("[RPPSID]", "Tbl_CCA", "[RPPSID] = " & [Search])) Then
MsgBox "This RPPSID does not exist in the Table", vbOKOnly, "RPPSID
Not
Found"
Exit Sub
Else
DoCmd.OpenForm "frmCCA"
DoCmd.GoToControl "RPPSID"
DoCmd.FindRecord Forms!frmSearchDialog!Search
End If

End Sub

The error message box works. I get this error on the DoCmd.GoToControl
"RPPSID" line:
Error 2109 - There is no field named "RPPSID" in the current record

This is the field in my table that I am searching for the record. I was
reading about control sources yesterday, but was not successful in getting
this line to work.

The Macro I originally had tied to the button was OpenForm, the name of the
form is frmCCA, View Form, Where Condition -
[Tbl_CCA]![RPPSID]=[Forms]![frmSearch]![Search]

This found the correct record. If the RPPSID was not in the table the form
would open to a new blank record, I did not want that. I wanted to put
another button on the search form for creating a new record that would bring
up an empty for for data entry.

Again, thank you, Cindy


AlCamp said:
Cindy,
If your old macro did the Find correctly, then just convert that
sequence of macro instructions into VB code behind your FindID button. You
don't tell me what those macro functions are, so I'll just have to guess...

IF you have a seprate "dialog form" (form Search)** where you are
entering your search criteria then you'll have to use a FIND sequence like
this...
DoCmd.OpenForm "YourIDForm"
DoCmd.GoToControl "AGY_ID"
DoCmd.FindRecord Forms!YourDialogFormName!Search
This is just a "typical" sequence... there's no way that I can know
exactly how you have everything set up. Use it as a guide, and use Help to
understand the Functions, their associated syntax, and then customize this
my process for your particular needs.

**Right now you I think you have a form named Search, and a field on that
form called Search... (duplicates)... that's not good. Your dialog form
should be named frmSearchDialog for clarity, and your field on the form for
the AGY_ID value should be renamed to FindAGY_ID. (and change the code
above accordingly)
hth
Al Camp

Cindy said:
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

AlCamp said:
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
Click to expand...
Click to expand...
Click to expand...
 
Cindy,
I would much rather have stuck with searching for AGY_ID... now, we're
doing RPPSID??
Please... let's try to stick to one problem at a time.

frmCCA should have text control on it named RPPSID. The ControlSource
for that is field RPPSID from the tbl_CCA.

Do you have that?

Al Camp


Cindy said:
Al, thank you so much for the time you are takeing to helping me. Below
is
the code I currently have:

Private Sub cmdSearchRPPSID_Click()

If IsNull(DLookup("[RPPSID]", "Tbl_CCA", "[RPPSID] = " & [Search])) Then
MsgBox "This RPPSID does not exist in the Table", vbOKOnly, "RPPSID
Not
Found"
Exit Sub
Else
DoCmd.OpenForm "frmCCA"
DoCmd.GoToControl "RPPSID"
DoCmd.FindRecord Forms!frmSearchDialog!Search
End If

End Sub

The error message box works. I get this error on the DoCmd.GoToControl
"RPPSID" line:
Error 2109 - There is no field named "RPPSID" in the current record

This is the field in my table that I am searching for the record. I was
reading about control sources yesterday, but was not successful in getting
this line to work.

The Macro I originally had tied to the button was OpenForm, the name of
the
form is frmCCA, View Form, Where Condition -
[Tbl_CCA]![RPPSID]=[Forms]![frmSearch]![Search]

This found the correct record. If the RPPSID was not in the table the
form
would open to a new blank record, I did not want that. I wanted to put
another button on the search form for creating a new record that would
bring
up an empty for for data entry.

Again, thank you, Cindy


AlCamp said:
Cindy,
If your old macro did the Find correctly, then just convert that
sequence of macro instructions into VB code behind your FindID button.
You
don't tell me what those macro functions are, so I'll just have to
guess...

IF you have a seprate "dialog form" (form Search)** where you are
entering your search criteria then you'll have to use a FIND sequence
like
this...
DoCmd.OpenForm "YourIDForm"
DoCmd.GoToControl "AGY_ID"
DoCmd.FindRecord Forms!YourDialogFormName!Search
This is just a "typical" sequence... there's no way that I can know
exactly how you have everything set up. Use it as a guide, and use Help
to
understand the Functions, their associated syntax, and then customize
this
my process for your particular needs.

**Right now you I think you have a form named Search, and a field on
that
form called Search... (duplicates)... that's not good. Your dialog form
should be named frmSearchDialog for clarity, and your field on the form
for
the AGY_ID value should be renamed to FindAGY_ID. (and change the code
above accordingly)
hth
Al Camp

Cindy said:
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
Click to expand...
Click to expand...
Click to expand...
 
Regarding my previous response... try this instead. It should work whether
you actually have [AGY_ID] on the form or not

You wrote...
The Macro I originally had tied to the button was OpenForm, the name of
the
form is frmCCA, View Form, Where Condition -
[Tbl_CCA]![RPPSID]=[Forms]![frmSearch]![Search]
Click to expand...

OK... this is good info.... this is what you had in the old macro. Now
we just have to translate it to VB. I'll use the old AGY_ID code...

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
'------------- try this
DoCmd.OpenForm "frmACC", , , "[AGY_ID] = Forms!frmSearch!Search"
End If
End Sub

Al Camp
 
That worked, thank you. I had a question about another db I am working on.
Can I use that same type of code to search for multiple records with the same
id. maybe a search by id and a date range that will bring up a list of all
occurences that meet that criteria then select the correct record to go to.

AlCamp said:
Regarding my previous response... try this instead. It should work whether
you actually have [AGY_ID] on the form or not

You wrote...
The Macro I originally had tied to the button was OpenForm, the name of
the
form is frmCCA, View Form, Where Condition -
[Tbl_CCA]![RPPSID]=[Forms]![frmSearch]![Search]
Click to expand...

OK... this is good info.... this is what you had in the old macro. Now
we just have to translate it to VB. I'll use the old AGY_ID code...

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
'------------- try this
DoCmd.OpenForm "frmACC", , , "[AGY_ID] = Forms!frmSearch!Search"
End If
End Sub

Al Camp
Click to expand...
 
If you're searching a field with that contains only "unique" values...
(probably indexed with no dupes) this code will find the exact record.

If you're searching a field that does not have "unique" values (contains
duplicate values) this code will find the First instance of that value.

A method I use for finding multiple answers to a recordset criteria is to
"filter" the dataset for that value. ONLY those records that meet the
criteria are returned, and they are contiguous, allowing for easy browsing
from one to another.

Check out Filter and Filteron functions in Help.

Al Camp

Cindy said:
That worked, thank you. I had a question about another db I am working
on.
Can I use that same type of code to search for multiple records with the
same
id. maybe a search by id and a date range that will bring up a list of
all
occurences that meet that criteria then select the correct record to go
to.

AlCamp said:
Regarding my previous response... try this instead. It should work
whether
you actually have [AGY_ID] on the form or not

You wrote...
The Macro I originally had tied to the button was OpenForm, the name of
the
form is frmCCA, View Form, Where Condition -
[Tbl_CCA]![RPPSID]=[Forms]![frmSearch]![Search]
Click to expand...

OK... this is good info.... this is what you had in the old macro.
Now
we just have to translate it to VB. I'll use the old AGY_ID code...

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
'------------- try this
DoCmd.OpenForm "frmACC", , , "[AGY_ID] = Forms!frmSearch!Search"
End If
End Sub

Al Camp
Click to expand...
Click to expand...
 
Back
Top