use combo box to lookup/enter data into form for textbox

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

Guest

I have a table with multiple fields and I am trying to have a form update or
recall information from this table so an individual can use the userfriendly
form to enter new data or edit existing data.

I would like to use a combo box with the Last Name field and recall the
record related to the last name. If it does not exist then new data can be
entered.

Thanks in advance for your help

Mike
 
I would like to use a combo box with the Last Name field and recall the
record related to the last name. If it does not exist then new data can be
entered.
Click to expand...

and if you have six people all with the last name Jones in the
table...?

Use the combo box's AfterUpdate and NotInList events to do this. It
must be an UNBOUND combo (otherwise selecting an entry will change the
control source field to the selected name for whatever record the user
is now on).

Private Sub cboFindName_AfterUpdate()
Dim rs As DAO.Recordset
Set rs = Me.RecordsetClone
rs.FindFirst "[LastName] = """ & Me!cboFindName & """"
If rs.NoMatch Then
DoCmd.GoToRecord acForm, Me.Name, acNewRecord
Else
Me.Bookmark = rs.Bookmark
End If
Set rs = Nothing
End Sub

Private Sub cboFindName_NotInList(Newdata As String, Response as
Integer)
Response = acDataErrContinue
DoCmd.GoToRecord acForm, Me.Name, acNewRecord
End Sub

John W. Vinson[MVP]
 
John
Thanks for your response. I have entered the code but am not to sure what
the line "Dim rs As DAO.Recordset" means.

When I try to use the Last Name box the following error come up

"Compile error - user defined type not defined

What is this in resonse for.

Thanks

Mike

John Vinson said:
I would like to use a combo box with the Last Name field and recall the
record related to the last name. If it does not exist then new data can be
entered.
Click to expand...

and if you have six people all with the last name Jones in the
table...?

Use the combo box's AfterUpdate and NotInList events to do this. It
must be an UNBOUND combo (otherwise selecting an entry will change the
control source field to the selected name for whatever record the user
is now on).

Private Sub cboFindName_AfterUpdate()
Dim rs As DAO.Recordset
Set rs = Me.RecordsetClone
rs.FindFirst "[LastName] = """ & Me!cboFindName & """"
If rs.NoMatch Then
DoCmd.GoToRecord acForm, Me.Name, acNewRecord
Else
Me.Bookmark = rs.Bookmark
End If
Set rs = Nothing
End Sub

Private Sub cboFindName_NotInList(Newdata As String, Response as
Integer)
Response = acDataErrContinue
DoCmd.GoToRecord acForm, Me.Name, acNewRecord
End Sub

John W. Vinson[MVP]
Click to expand...
 
John
Thanks for your response. I have entered the code but am not to sure what
the line "Dim rs As DAO.Recordset" means.

When I try to use the Last Name box the following error come up

"Compile error - user defined type not defined

What is this in resonse for.
Click to expand...

You presumably have Access2000 or an early version of 2002 - these
versions did not default to using the DAO object library.

Open the VBA editor and select Tools... References from the menu.
Scroll down and find the line

Microsoft DAO 3.6 Object Library <or highest version>

Check it. You may also want to uncheck the ADO reference Microsoft
ActiveX Data Objects if you're not using any ADO code; DAO and ADO are
two overlapping technologies.

John W. Vinson[MVP]
 
John,

I have the combo box pulling up records and updating other fields from the
table the form pulls from.

However, If a record is not present I cannot update the other field nor
create a new record. How can I do this using the combo box and form
 
Back
Top