Search records by unbound combo box - please help!

[Bellyjeans]

Hi everybody,

I have an unbound combo box (cboSearchByLastName) on a form that
searches for specific records based on last name. It's searching just
fine, but I'd like to refine it a bit.

What I'd like it to do is if the record is not in the list, a message
box pops up with a yes and a no button that says, "Person not found.
Add this person as a new record?". If the user clicks "Yes", a new
record would be created and the information that the user entered into
the unbound combo box to search would automatically be entered into
the bound textbox "txtLastName" on the form. The unbound combo box
would then be cleared of its entry.

If the user clicks "No" on the message box, the unbound combo box
would be cleared of its entry.

Any help that you guys could provide would be of great assistance.

Thanks!

 

[Frank H]

If you are using Access 2007, this is one of the nicest new features. The
combo box has properties to handle this; the "List items Edit Form" handles
exactly what you are talking about.

If A2003 or earlier, you need to create an Event Procedure or macro in the
combo box's NotInList event. If you searched the microsoft knowledge base for
"NotInList" you'd probably find code for this; it's fairly common (although I
don't have a sample right at hand.)

 

[Bellyjeans]

If you are using Access 2007, this is one of the nicest new features. The
combo box has properties to handle this; the "List items Edit Form" handles
exactly what you are talking about.

If A2003 or earlier, you need to create an Event Procedure or macro in the
combo box's NotInList event. If you searched the microsoft knowledge basefor
"NotInList" you'd probably find code for this; it's fairly common (although I
don't have a sample right at hand.)
--
Frank H
Rockford, IL










- Show quoted text -

Unfortunately I'm running 2003. Maybe I'm not looking hard enough,
but I can't find anything on the Knowledge Base...

 

[Frank H]

Subject: Re: update combo box on form close 9/22/2009 2:45 AM PST
The above items appears to be very similar what you are looking for. In
Access older than 2007, this is how you need to do it, by using the NotInList
event. Code is included.
Good Luck!

 

[Bellyjeans]

Subject: Re: update combo box on form close   9/22/2009 2:45 AM PST
The above items appears to be very similar what you are looking for. In
Access older than 2007, this is how you need to do it, by using the NotInList
event. Code is included.
Good Luck!
--
Frank H
Rockford, IL






- Show quoted text -

Hi again,

Ok, I found this code on the knowledge base:

Dim Db As DAO.Database
Dim Rs As DAO.Recordset
Dim Msg As String

Msg = "'" & NewData & "' is not in the list." & vbCr & vbCr
Msg = Msg & "Do you want to add it?"
If MsgBox(Msg, vbQuestion + vbYesNo) = vbNo Then
Response = acDataErrContinue
MsgBox "Try again."
Else
Set Db = CurrentDb
Set Rs = Db.OpenRecordset("Rates", dbOpenDynaset)

Rs.AddNew
Rs![Rates] = NewData
Rs.Update
Response = acDataErrAdded

End If


Now I understand that with this code, if one types in a value that
isn't currently listed in the combo box, it would ask you if you would
like to add it to the list; if not, it asks you to try again. How
would I adapt this to my needs? Instead of adding the name to the
list in the combo box, what I'd like it to do instead is when I click
on "Yes", it would create a new record and populate my txtLastName
with the value that I've entered into the combo box and clear out the
value in the combo box. When "No" is clicked, it would simply clear
out the value in the combo box.

Thanks!

 

[Frank H]

Assuming your combo box name is cboPickAName, you would have something like
the following. (You would find the NotInList event procedure and enter the
code inside the procedure, so my first line and last line would already be
provided for you.) I don't have time to work out the exact code, but the
major idea is as follows

sub cboPickAName_NotInList()
Dim Msg As String

Msg = "'" & NewData & "' is not in the list." & vbCr & vbCr
Msg = Msg & "Do you want to add it?"
If MsgBox(Msg, vbQuestion + vbYesNo) = vbNo Then

cboPickAName.text = ""

Else
(you will have to find the correct commands, but this gets you the idea.
I'm not setup at this pc to get the exact syntax.)

docmd.gotonewrecord (or something like that)

(if you don't find it under doCmd, then try RunCommand() )

(and then once the form has gone to a new record...)
txtlastname = newdata

End If
end sub

Sorry,i've got to get going. if docmd or runcommand don't work there might
be a method of the form, so type ME. and see what pops up.
--
Frank H
Rockford, IL

Subject: Re: update combo box on form close 9/22/2009 2:45 AM PST
The above items appears to be very similar what you are looking for. In
Access older than 2007, this is how you need to do it, by using the NotInList
event. Code is included.
Good Luck!
--
Frank H
Rockford, IL














- Show quoted text -

Hi again,

Ok, I found this code on the knowledge base:

Dim Db As DAO.Database
Dim Rs As DAO.Recordset
Dim Msg As String

Msg = "'" & NewData & "' is not in the list." & vbCr & vbCr
Msg = Msg & "Do you want to add it?"
If MsgBox(Msg, vbQuestion + vbYesNo) = vbNo Then
Response = acDataErrContinue
MsgBox "Try again."
Else
Set Db = CurrentDb
Set Rs = Db.OpenRecordset("Rates", dbOpenDynaset)

Rs.AddNew
Rs![Rates] = NewData
Rs.Update
Response = acDataErrAdded

End If


Now I understand that with this code, if one types in a value that
isn't currently listed in the combo box, it would ask you if you would
like to add it to the list; if not, it asks you to try again. How
would I adapt this to my needs? Instead of adding the name to the
list in the combo box, what I'd like it to do instead is when I click
on "Yes", it would create a new record and populate my txtLastName
with the value that I've entered into the combo box and clear out the
value in the combo box. When "No" is clicked, it would simply clear
out the value in the combo box.

Thanks!