Combo Box

  • Thread starter Thread starter LOgle via AccessMonster.com
  • Start date Start date
L

LOgle via AccessMonster.com

I have a form I am trying to use a combo box on in order to pull up the form
by employee name. I can choose the name in the combo box but my form just
stays on the same name. How can I choose a name from the combo box and have
it go to that name on my form?

On my form I have an EMP Stats table and the Subform is PIC form. The
primary key is the Emp ID.

Thank you!

--
Thank You!

LOgle

Message posted via AccessMonster.com
 
I get a compile error. User Derined type not defined. The highlighted part
is on

Sub CboMoveTo_AfterUpdate ()
Dim rs AS DAO.Recordset

Any Suggestions?



Allen said:
See:
Using a Combo Box to Find Records
at:
http://allenbrowne.com/ser-03.html
I have a form I am trying to use a combo box on in order to pull up the
form
Click to expand...
[quoted text clipped - 5 lines]
On my form I have an EMP Stats table and the Subform is PIC form. The
primary key is the Emp ID.
Click to expand...
Click to expand...

--
Thank You!

LOgle

Message posted via AccessMonster.com
 
You may not have the reference set to the DAO Library (You can check
this by hitting Ctrl+G to bring up the code window, and then going to
Tolls--> References--> and seeing if there are any items checked with
Microsoft DAO Library). Is the combo box supposed to pop up a form
based off the combo box selection, or does it need only to populate the
current form it's on?
 
I just want it to populate the current form that it is on. I will check
this.

Keith said:
You may not have the reference set to the DAO Library (You can check
this by hitting Ctrl+G to bring up the code window, and then going to
Tolls--> References--> and seeing if there are any items checked with
Microsoft DAO Library). Is the combo box supposed to pop up a form
based off the combo box selection, or does it need only to populate the
current form it's on?
Click to expand...

--
Thank You!

LOgle

Message posted via AccessMonster.com
 
Private Sub cboMoveTo_AfterUpdate()
Dim rs As DAO.Recordset

If Not IsNull(Me.cboMoveTo) Then
'Save before move.
If Me.Dirty Then
Me.Dirty = False
End If
'Search in the clone set.
Set rs = Me.RecordsetClone
rs.FindFirst "[EMPSTATS.EMPID] = " & Me.cboMoveTo
If rs.NoMatch Then
MsgBox "NotFound: filtered?"
Else
'Display the found record in the form.
Me.Bookmark = rs.Bookmark
End If
Set rs = Nothing
End If



End Sub

This is what I have now and it is stopping at : Set rs = Me.RecordsetClone
the only thing I changed is the Table name because mine needs to verify the
EmpStats.EmpId and then match it to the corresponding last name. In the
example you use [CustomerID]

Can you see something wrong with my code?

Thanks!!
I just want it to populate the current form that it is on. I will check
this.
Click to expand...

--
Thank You!

LOgle

Message posted via AccessMonster.com
 
Look at these properties so your combo:
Bound Column
Row Source
Column Widths

Bound Column will normally be 1.
If so, the first field in the Row Source must be EmpID.
If you do not wish to show this field to the user, you can set the Column
Widths to zero.

The code also assumes that EmpID a Number type field. If it is a Text field,
you need extra quotes:
rs.FindFirst "[EMPSTATS.EMPID] = """ & Me.cboMoveTo & """"

I take it you have added a reference to:
Microsoft DAO 3.6 Library
under Tools | References (from code window.)

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

LOgle via AccessMonster.com said:
Private Sub cboMoveTo_AfterUpdate()
Dim rs As DAO.Recordset

If Not IsNull(Me.cboMoveTo) Then
'Save before move.
If Me.Dirty Then
Me.Dirty = False
End If
'Search in the clone set.
Set rs = Me.RecordsetClone
rs.FindFirst "[EMPSTATS.EMPID] = " & Me.cboMoveTo
If rs.NoMatch Then
MsgBox "NotFound: filtered?"
Else
'Display the found record in the form.
Me.Bookmark = rs.Bookmark
End If
Set rs = Nothing
End If
End Sub

This is what I have now and it is stopping at : Set rs =
Me.RecordsetClone
the only thing I changed is the Table name because mine needs to verify
the
EmpStats.EmpId and then match it to the corresponding last name. In the
example you use [CustomerID]

Can you see something wrong with my code?

Thanks!!
I just want it to populate the current form that it is on. I will check
this.
Click to expand...
Click to expand...
 
I deleted my combo box off of the form and started over from fresh. I did
this I adjusted all the properties in my combo box so that my combo box had
my names showing by last then first with the EMPID hidden but yet bound by
the EMP ID.

Then I went to the Event Procedure of my Combo Box and did this:


Private Sub Combo31_AfterUpdate()
' Find the record that matches the control.
Dim rs As Object

Set rs = Me.Recordset.Clone
rs.FindFirst "[EMPID] = '" & Me![Combo31] & "'"
Me.Bookmark = rs.Bookmark
End Sub

After I entered this code in it worked like I wanted it to! I still don't
know why it didn't work the way you suggested but I did reference the DAO
Library. Anyway It now works for me!!! Thank you so much for all your
suggestions and all your assistance. I am going to study these combo boxes
more throughly as I want to learn exactly how they work. I would really like
to learn more on the coding part but I am not sure what class to take. I
think maybe a VB class would help me in times like this. What do you think?
Thanks Again!!




Allen said:
Look at these properties so your combo:
Bound Column
Row Source
Column Widths

Bound Column will normally be 1.
If so, the first field in the Row Source must be EmpID.
If you do not wish to show this field to the user, you can set the Column
Widths to zero.

The code also assumes that EmpID a Number type field. If it is a Text field,
you need extra quotes:
rs.FindFirst "[EMPSTATS.EMPID] = """ & Me.cboMoveTo & """"

I take it you have added a reference to:
Microsoft DAO 3.6 Library
under Tools | References (from code window.)
Private Sub cboMoveTo_AfterUpdate()
Dim rs As DAO.Recordset
Click to expand...
[quoted text clipped - 37 lines]
Click to expand...

--
Thank You!

LOgle

Message posted via AccessMonster.com
 
Back
Top