Open Form To Record Based On Value in Another Field

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

Guest

I have an Access 2003 tab'd form named GrievTracking. I want to open a form
named UnionRepsList and have it go the record based on the data in the field
named RepName. When I enter data for RepName field the next field is
RepPhonenum. The "ON ENTER" event procedure for the field RepPhonenum opens
the form UnionRepsList and goes the first record. I would like it to open and
go to the record for data in the RepName field. I have the "ON ENTER" event
procedure to: DoCmd.openform "UnionRepsList", , , , , acDialog

Thanks for the help.
 
=?Utf-8?B?cmR3MTI2MEBtc24uY29tLihkb25vdHNwYW0p?=
I have an Access 2003 tab'd form named GrievTracking. I want
to open a form named UnionRepsList and have it go the record
based on the data in the field named RepName. When I enter
data for RepName field the next field is RepPhonenum. The "ON
ENTER" event procedure for the field RepPhonenum opens the
form UnionRepsList and goes the first record. I would like it
to open and go to the record for data in the RepName field. I
have the "ON ENTER" event procedure to: DoCmd.openform
"UnionRepsList", , , , , acDialog

Thanks for the help.
Click to expand...

The openform method allows you to filter the form to a selected
record. If that's acceptable,
Docmd.OpenForm "UnionRepslist",,,"Repname = """ & me.repname &
"""",,acdialog will work

If you want instead to still have all the reps' info available.
you could use the openargs parameter of the openform method, and
trigger a find in the reps form.

But have you considered using a combobox based on the reps table
to pick the rep's name, and phone number? No need to open the
freps' form at all.

BTW I'm Grievance commitee chair for CAW's local 188.
 
Bob:

Thanks for the response. I have not tried it yet. I did to some degree. My
first problem was to address the issue that a current union rep will at some
point no longer be a union rep. We must always know who represented the
grievant for that dispute at every step of the process. The database I
created allows a member to have only one type of membership (MemberType). If
I based the UnionRep table on a query that uses the membership type for the
union rep (steward) it would change the field's data once that rep's status
changed, I think. That caused me to create a table of all of the union reps
who have processed a grievance, and then create a combo box so that I could
make sure their names were entered uniformly.

To your suggestion, we have union reps that will only give us their cell or
their home phone numbers. I had a real estate problem on GrievTracking form
and did not want to add fields for their contact numbers (work, home, car,
cell, and e-mail address). Hence, the UnionRepList form that will allow the
person who (me right now) is entering the grievances to copy and paste one of
the contact numbers. Ideally, I would like to be able to doubleclick the
number selected on the UnionRepList form and have it paste/populate, whatever
is the proper method to the RepPhoneNum field on the GreivTracking form.
That’s beyond my ability to create it meself.

I have 3 or 4 MS Access books. I could not find an answer to my question.

I'm Treasurer for NALC branch (local) 142.

Again, thanks for the help.
 
=?Utf-8?B?cmR3MTI2MEBtc24uY29tLihkb25vdHNwYW0p?=
Bob:

Thanks for the response. I have not tried it yet. I did to
some degree. My first problem was to address the issue that a
current union rep will at some point no longer be a union rep.
We must always know who represented the grievant for that
dispute at every step of the process. The database I created
allows a member to have only one type of membership
(MemberType). If I based the UnionRep table on a query that
uses the membership type for the union rep (steward) it would
change the field's data once that rep's status changed, I
think. That caused me to create a table of all of the union
reps who have processed a grievance, and then create a combo
box so that I could make sure their names were entered
uniformly.

To your suggestion, we have union reps that will only give us
their cell or their home phone numbers. I had a real estate
problem on GrievTracking form and did not want to add fields
for their contact numbers (work, home, car, cell, and e-mail
address). Hence, the UnionRepList form that will allow the
person who (me right now) is entering the grievances to copy
and paste one of the contact numbers. Ideally, I would like to
be able to doubleclick the number selected on the UnionRepList
form and have it paste/populate, whatever is the proper method
to the RepPhoneNum field on the GreivTracking form. That’s
beyond my ability to create it meself.

I have 3 or 4 MS Access books. I could not find an answer to
my question.

I'm Treasurer for NALC branch (local) 142.

Again, thanks for the help.
Click to expand...

What you say about keeping the data about historical grievances
is valid. But you will only be assigning a grievance to a
current steward, so you can still pull the data from the members
table. You store that steward's ID in the grievances table. You
can then get lookup data like phone, cel # etc by lookup from
the members table based on the memberID field.

If you still need a stewards table, because sometimes a national
office rep will handle a grievance, you'd lookup the phone
number from that table instead of the members table.

In any case, the steward's nnme can be picked from a combobox.
that shows the active stewards when creating a new entry, and
will show the recorded steward when you call up an existing
grievance.

If you really need to store the phone number in the griewvances
table, you could put code in the combobox's afterupdate event to
stuff the phone number there.

Double-clicking on the stewards form and having it push the data
to the grievances form will require a lot more code, but if you
decide to do that, white me back and I'll give you some
pointers.
 
=?Utf-8?B?cmR3MTI2MEBtc24uY29tLihkb25vdHNwYW0p?=
Bob:

I tried your code. It resulted in an "Enter Parameter Value"
dialog box. No matter what data I entered I got the
UnionRepsList form in add a new record mode.
Click to expand...

That indicates some error in the name of the field. I had
guessed at repname, from your code I see it's unionrepname.
Anyway, it's now working, and the code looks fine.

Good luck, and may you win all your grievances.

So, after reading
elsewhere in this discussion group I found a similar question
that suggested the user could have obtained the code using the
command button wizard to open a form. I did and I got the
following code which works just fine. I then modified the code
to reflect that it is for an "On Enter" event procedure not
"On Click" event procedure and copied the code to the "On
Enter" event procedure. If anybody see's an error please let
me know.

On Error GoTo Err_UnionRepList_Enter
Dim stDocName As String
Dim stLinkCriteria As String
stDocName = "UnionRepsList"
stLinkCriteria = "[FullName]=" & "'" & Me![UnionRepName] &
"'" DoCmd.OpenForm stDocName, , , stLinkCriteria
Exit_UnionRepList_Enter:
Exit Sub
Err_UnionRepList_Enter:
MsgBox Err.Description
Resume Exit_UnionRepList_Enter
End Sub

Thanks for the help.
Click to expand...
 
Bob:

I tried your code. It resulted in an "Enter Parameter Value" dialog box. No
matter what data I entered I got the UnionRepsList form in add a new record
mode. So, after reading elsewhere in this discussion group I found a similar
question that suggested the user could have obtained the code using the
command button wizard to open a form. I did and I got the following code
which works just fine. I then modified the code to reflect that it is for an
"On Enter" event procedure not "On Click" event procedure and copied the code
to the "On Enter" event procedure. If anybody see's an error please let me
know.

On Error GoTo Err_UnionRepList_Enter
Dim stDocName As String
Dim stLinkCriteria As String
stDocName = "UnionRepsList"
stLinkCriteria = "[FullName]=" & "'" & Me![UnionRepName] & "'"
DoCmd.OpenForm stDocName, , , stLinkCriteria
Exit_UnionRepList_Enter:
Exit Sub
Err_UnionRepList_Enter:
MsgBox Err.Description
Resume Exit_UnionRepList_Enter
End Sub

Thanks for the help.
 
Back
Top