Drop down field on form

R

RMiller

I have a form 'Project Manager Details' based on a query that is based on two
tables 'Project' and 'Project Manager'. The Project table contains a field
'Job Code' which appears on the Project Manager Details form so that projects
can be chosen so that project managers can add their information. However,
when in an existing record in the form chosing a new job code from the drop
down does not display the information for the newly chosen job code, it only
edits the record that is being viewed.
I am little more than a novice Access user so please be gentle!
 
B

Beetle

It sounds like your combo box is bound to the Job Code field, which is
definitely wrong if you are just trying to find records that match the
selection made in the combo. You should have an *unbound* combo
box (nothing in it's Control Source) in, for example, the header of the
form. Then in the After Update event of the combo box you would add
code similar to the following;

Private Sub YourComboBox_AfterUpdate()

With Me.RecordsetClone
.FindFirst "[Job Code] = " & Me![YourComboBox]
If Not .NoMatch Then
Me.Bookmark = .Bookmark
End If
End With

End Sub

If the combo box is bound to the Job Code field, then everytime you make
a selection in it you are changing the underlying value in the record, which
is not what you want (at least if I understand your post correctly).
 
R

RMiller

You are totally understanding my question and that field was bound to the Job
Code field in the Project table. However, I have now deleted that and have
added the below code to After Update event and it still is not working. I'm
certain I've missed something you told me or maybe I'm not adding this code
to the appropriate place. sorry for being the slow kid.

Private Sub JobecodeLookup_AfterUpdate()

With Me.RecordsetClone
.FindFirst "[Job Code] = " & Me![JobCodeLookup]
If Not .NoMatch Then
Me.Bookmark = .Bookmark
End If
End With

End Sub

Beetle said:
It sounds like your combo box is bound to the Job Code field, which is
definitely wrong if you are just trying to find records that match the
selection made in the combo. You should have an *unbound* combo
box (nothing in it's Control Source) in, for example, the header of the
form. Then in the After Update event of the combo box you would add
code similar to the following;

Private Sub YourComboBox_AfterUpdate()

With Me.RecordsetClone
.FindFirst "[Job Code] = " & Me![YourComboBox]
If Not .NoMatch Then
Me.Bookmark = .Bookmark
End If
End With

End Sub

If the combo box is bound to the Job Code field, then everytime you make
a selection in it you are changing the underlying value in the record, which
is not what you want (at least if I understand your post correctly).
--
_________

Sean Bailey


RMiller said:
I have a form 'Project Manager Details' based on a query that is based on two
tables 'Project' and 'Project Manager'. The Project table contains a field
'Job Code' which appears on the Project Manager Details form so that projects
can be chosen so that project managers can add their information. However,
when in an existing record in the form chosing a new job code from the drop
down does not display the information for the newly chosen job code, it only
edits the record that is being viewed.
I am little more than a novice Access user so please be gentle!
 
B

Beetle

I'm not sure what you mean when you say it doesn't work (i.e. is there
an error message? or nothing happens at all?) but here are some things to
check.

1) The Job Code field still needs to exist in the detail section of your
form,
probably with just a text box bound to it. Set the visible property to No
if you don't want your users to see it.

2) The code example I provided assumes that the bound column of your
combo box is an integer data type. If it is a text data type, then the
code
will need to have quotes added like below;


Private Sub YourComboBox_AfterUpdate()

With Me.RecordsetClone
.FindFirst "[Job Code] = """ & Me![YourComboBox] & """"
If Not .NoMatch Then
Me.Bookmark = .Bookmark
End If
End With

End Sub

Note - When I say the "bound column of your combo box", don't confuse
that with the combo box actually being bound to a field. These
are two different things.


--
_________

Sean Bailey


RMiller said:
You are totally understanding my question and that field was bound to the Job
Code field in the Project table. However, I have now deleted that and have
added the below code to After Update event and it still is not working. I'm
certain I've missed something you told me or maybe I'm not adding this code
to the appropriate place. sorry for being the slow kid.

Private Sub JobecodeLookup_AfterUpdate()

With Me.RecordsetClone
.FindFirst "[Job Code] = " & Me![JobCodeLookup]
If Not .NoMatch Then
Me.Bookmark = .Bookmark
End If
End With

End Sub

Beetle said:
It sounds like your combo box is bound to the Job Code field, which is
definitely wrong if you are just trying to find records that match the
selection made in the combo. You should have an *unbound* combo
box (nothing in it's Control Source) in, for example, the header of the
form. Then in the After Update event of the combo box you would add
code similar to the following;

Private Sub YourComboBox_AfterUpdate()

With Me.RecordsetClone
.FindFirst "[Job Code] = " & Me![YourComboBox]
If Not .NoMatch Then
Me.Bookmark = .Bookmark
End If
End With

End Sub

If the combo box is bound to the Job Code field, then everytime you make
a selection in it you are changing the underlying value in the record, which
is not what you want (at least if I understand your post correctly).
--
_________

Sean Bailey


RMiller said:
I have a form 'Project Manager Details' based on a query that is based on two
tables 'Project' and 'Project Manager'. The Project table contains a field
'Job Code' which appears on the Project Manager Details form so that projects
can be chosen so that project managers can add their information. However,
when in an existing record in the form chosing a new job code from the drop
down does not display the information for the newly chosen job code, it only
edits the record that is being viewed.
I am little more than a novice Access user so please be gentle!
 
N

NEC-2008

Mr. Miller, Did you check your spelling in the private sub line? ""Job E
codelookup""
Spelling may be off a bit.

RMiller said:
You are totally understanding my question and that field was bound to the Job
Code field in the Project table. However, I have now deleted that and have
added the below code to After Update event and it still is not working. I'm
certain I've missed something you told me or maybe I'm not adding this code
to the appropriate place. sorry for being the slow kid.

Private Sub JobecodeLookup_AfterUpdate()

With Me.RecordsetClone
.FindFirst "[Job Code] = " & Me![JobCodeLookup]
If Not .NoMatch Then
Me.Bookmark = .Bookmark
End If
End With

End Sub

Beetle said:
It sounds like your combo box is bound to the Job Code field, which is
definitely wrong if you are just trying to find records that match the
selection made in the combo. You should have an *unbound* combo
box (nothing in it's Control Source) in, for example, the header of the
form. Then in the After Update event of the combo box you would add
code similar to the following;

Private Sub YourComboBox_AfterUpdate()

With Me.RecordsetClone
.FindFirst "[Job Code] = " & Me![YourComboBox]
If Not .NoMatch Then
Me.Bookmark = .Bookmark
End If
End With

End Sub

If the combo box is bound to the Job Code field, then everytime you make
a selection in it you are changing the underlying value in the record, which
is not what you want (at least if I understand your post correctly).
--
_________

Sean Bailey


RMiller said:
I have a form 'Project Manager Details' based on a query that is based on two
tables 'Project' and 'Project Manager'. The Project table contains a field
'Job Code' which appears on the Project Manager Details form so that projects
can be chosen so that project managers can add their information. However,
when in an existing record in the form chosing a new job code from the drop
down does not display the information for the newly chosen job code, it only
edits the record that is being viewed.
I am little more than a novice Access user so please be gentle!
 
R

RMiller

I did have a typo that I fixed and now I get the following error message:

The expression After Update you entered as the even property setting
produced the following error: Ambiguous name detected:
Jobcodelookup_AfterUpdate.

Beetle said:
I'm not sure what you mean when you say it doesn't work (i.e. is there
an error message? or nothing happens at all?) but here are some things to
check.

1) The Job Code field still needs to exist in the detail section of your
form,
probably with just a text box bound to it. Set the visible property to No
if you don't want your users to see it.

2) The code example I provided assumes that the bound column of your
combo box is an integer data type. If it is a text data type, then the
code
will need to have quotes added like below;


Private Sub YourComboBox_AfterUpdate()

With Me.RecordsetClone
.FindFirst "[Job Code] = """ & Me![YourComboBox] & """"
If Not .NoMatch Then
Me.Bookmark = .Bookmark
End If
End With

End Sub

Note - When I say the "bound column of your combo box", don't confuse
that with the combo box actually being bound to a field. These
are two different things.


--
_________

Sean Bailey


RMiller said:
You are totally understanding my question and that field was bound to the Job
Code field in the Project table. However, I have now deleted that and have
added the below code to After Update event and it still is not working. I'm
certain I've missed something you told me or maybe I'm not adding this code
to the appropriate place. sorry for being the slow kid.

Private Sub JobecodeLookup_AfterUpdate()

With Me.RecordsetClone
.FindFirst "[Job Code] = " & Me![JobCodeLookup]
If Not .NoMatch Then
Me.Bookmark = .Bookmark
End If
End With

End Sub

Beetle said:
It sounds like your combo box is bound to the Job Code field, which is
definitely wrong if you are just trying to find records that match the
selection made in the combo. You should have an *unbound* combo
box (nothing in it's Control Source) in, for example, the header of the
form. Then in the After Update event of the combo box you would add
code similar to the following;

Private Sub YourComboBox_AfterUpdate()

With Me.RecordsetClone
.FindFirst "[Job Code] = " & Me![YourComboBox]
If Not .NoMatch Then
Me.Bookmark = .Bookmark
End If
End With

End Sub

If the combo box is bound to the Job Code field, then everytime you make
a selection in it you are changing the underlying value in the record, which
is not what you want (at least if I understand your post correctly).
--
_________

Sean Bailey


:

I have a form 'Project Manager Details' based on a query that is based on two
tables 'Project' and 'Project Manager'. The Project table contains a field
'Job Code' which appears on the Project Manager Details form so that projects
can be chosen so that project managers can add their information. However,
when in an existing record in the form chosing a new job code from the drop
down does not display the information for the newly chosen job code, it only
edits the record that is being viewed.
I am little more than a novice Access user so please be gentle!
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top