Combobox needs to update table

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

Guest

I have a combobox that sources a table. If the user types a name that is not
on the list, how do I get that name posted to the original table? Thanks in
advance for your help!

P
 
Hi.
If the user types a name that is not
on the list, how do I get that name posted to the original table?
Click to expand...

If you don't already have a form that allows data entry for this table, then
create one. The following example will use the frmContacts form to add a
record to the tblContacts table, which the form is bound to via a query. In
this form's OnLoad( ) event, paste the following code:

' * * * * Start Code * * * *

Private Sub Form_Load()

On Error GoTo ErrHandler

If ((Me.OpenArgs = "New") And Not IsNull(Me!txtID)) Then
DoCmd.GoToRecord , , acNewRec
End If

Exit Sub

ErrHandler:

MsgBox "Error in Form_Load( ) in " & Me.Name & " form." & _
vbCrLf & vbCrLf & _
"Error #" & Err.Number & vbCrLf & Err.Description
Err.Clear

End Sub

' * * * * End Code * * * *

txtID is the text box bound to the primary key field, so you may need to
change it to suit your form. Next, for the form with the combo box, in the
combo box's OnNotInList( ) event, paste the following code:

' * * * * Start Code * * * *

Private Sub cboContact_NotInList(NewData As String, Response As Integer)

On Error GoTo ErrHandler

MsgBox "Please double-click this field" & vbCrLf & _
"to add a new entry to the list.", _
vbInformation + vbOKOnly, "No Matching Record"
Response = acDataErrContinue

Exit Sub

ErrHandler:

MsgBox "Error in cboContact_NotInList( ) in " & Me.Name & " form." & _
vbCrLf & vbCrLf & _
"Error #" & Err.Number & vbCrLf & Err.Description
Err.Clear

End Sub

' * * * * End Code * * * *

cboContact is the name of the combo box containing the list of items stored
in the table that you want to add a new record to. Next, in the combo box's
OnDblClick( ) event, paste the following code:

' * * * * Start Code * * * *

Private Sub cboContact_DblClick(Cancel As Integer)

On Error GoTo ErrHandler

Dim nContactID As Long

If (IsNull(Me!cboContact)) Then
Me!cboContact.Text = ""
Else
nContactID = Me!cboContact
Me!cboContact = Null
End If

DoCmd.OpenForm "frmContacts", , , , , acDialog, "New"
Me!cboContact.Requery ' Get all records.

If (nContactID <> 0) Then
Me!cboContact = nContactID ' Reset to original row.
End If

Exit Sub

ErrHandler:

MsgBox "Error in cboContact_DblClick( ) in " & Me.Name & " form." & _
vbCrLf & vbCrLf & _
"Error #" & Err.Number & vbCrLf & Err.Description
Err.Clear

End Sub

' * * * * End Code * * * *

Save both forms and compile the code. Open the form with the combo box in
Form View and double-click on the combo box to see the other form open,
awaiting a new entry to be added to the original table. Once the new record
has been typed, close that form to return to the form with the combo box.
The combo box will now have the newest record in the list, ready to be
selected by the user.

HTH.

Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips.

(Please remove ZERO_SPAM from my reply E-mail address, so that a message
will be forwarded to me.)
Beware to those who use munged addresses: known newsgroup E-mail harvesters
for spammers are (e-mail address removed) and (e-mail address removed)

- - -
When you see correct answers to your question posted in Microsoft's Online
Community, please sign in to the Community and mark these posts as "Answers,"
so that all may benefit by filtering on "Answered questions" and quickly
finding the right answers to similar questions. Remember that questions
answered the quickest are often from those who have a history of rewarding
the contributors who have taken the time to answer questions correctly.
 
Hi,
I found this old email from you where you helped someone update a combo box
table when he entered something in a control that wasn't in the combo box
table. I need to do the same thing.
I am trying to do it using a Macro (since I don't know the arcane VB
language very well). I ALMOST have it but I am encountering a "glitch".
When someone enters a name in the control that is NOT in the combo box list,
the combo box's table automatically pops up because I have a macro in the
"Onnotinlist" event of the control.
The macro has only three commands. [1.] "Setvalue" to set the value of the
control to NULL, (I don't even know if I need this Setvalue) [2.] "OPEN
TABLE" to open the combo box table in Modal mode. [3.] "REFRESH" (F9) to
refresh the control after closing the table. I enter the new name in the
table and close the table. The combo box table is a modal table that pauses
the macro until I close the table. When I close the table, the macro
continues with a "refresh" command. Everything seems to work EXCEPT after
the "refresh" I get an error telling me the name is not in the list. After
hitting "OK" on the error message, I click on the combo box and VOILA! the
new name IS in the combo box table which I enter into the control and go on
my merry way.
The "glitch" is that final error message. Why am I getting that error
message when the new name IS in the combo box table? It is a small
annoyance to clear the error but the error may confuse a user into thinking
something happened and he may not continue. Any help would be appreciated.
I would like to continue using the Macro, since it ALMOST works, instead of
the code you graciously provided to the previous user.

Thanks
 
Hi, Anthony.

This thread is a few months old, so it's only by chance that I spotted your
new question today. It usually isn't a good idea to "tag onto" someone
else's question, because very few people are likely monitoring the thread.
And when a person has time to work on a question on one particular day, it
doesn't mean that he has time to work on someone else's question on some
other particular day (especially weeks later), even if the question is
similar.

For the best chance of getting a quick response, post a new question in the
newsgroups so that everyone who is answering questions "right now" will see
your question at the top of their newsreaders as soon as it's posted to
UseNet.

And I recommend posting your new question in the GettingStarted or Macros
newsgroups, not the Forms newsgroup, because whoever responds to your
question here will likely give you the same advice I will: get rid of the
training wheels and learn VBA, because you won't be able to build a complex,
yet stable, database application with just macros.

You can start learning by selecting the name of the macro in the Database
Window, then selecting the Tools -> Macro -> Convert Macro to Visual Basic
menu. A new module will be created, and you'll be able to see the
equivalent VBA commands for the macro's actions and arguments, and you'll
realize it's not so difficult after all.

Good luck.
Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips.


Anthony said:
Hi,
I found this old email from you where you helped someone update a combo
box
table when he entered something in a control that wasn't in the combo box
table. I need to do the same thing.
I am trying to do it using a Macro (since I don't know the arcane VB
language very well). I ALMOST have it but I am encountering a "glitch".
When someone enters a name in the control that is NOT in the combo box
list,
the combo box's table automatically pops up because I have a macro in the
"Onnotinlist" event of the control.
The macro has only three commands. [1.] "Setvalue" to set the value of
the
control to NULL, (I don't even know if I need this Setvalue) [2.] "OPEN
TABLE" to open the combo box table in Modal mode. [3.] "REFRESH" (F9) to
refresh the control after closing the table. I enter the new name in the
table and close the table. The combo box table is a modal table that
pauses
the macro until I close the table. When I close the table, the macro
continues with a "refresh" command. Everything seems to work EXCEPT after
the "refresh" I get an error telling me the name is not in the list.
After
hitting "OK" on the error message, I click on the combo box and VOILA!
the
new name IS in the combo box table which I enter into the control and go
on
my merry way.
The "glitch" is that final error message. Why am I getting that error
message when the new name IS in the combo box table? It is a small
annoyance to clear the error but the error may confuse a user into
thinking
something happened and he may not continue. Any help would be
appreciated.
I would like to continue using the Macro, since it ALMOST works, instead
of
the code you graciously provided to the previous user.

Thanks

'69 Camaro said:
Hi.


If you don't already have a form that allows data entry for this table,
then
create one. The following example will use the frmContacts form to add a
record to the tblContacts table, which the form is bound to via a query.
In
this form's OnLoad( ) event, paste the following code:

' * * * * Start Code * * * *

Private Sub Form_Load()

On Error GoTo ErrHandler

If ((Me.OpenArgs = "New") And Not IsNull(Me!txtID)) Then
DoCmd.GoToRecord , , acNewRec
End If

Exit Sub

ErrHandler:

MsgBox "Error in Form_Load( ) in " & Me.Name & " form." & _
vbCrLf & vbCrLf & _
"Error #" & Err.Number & vbCrLf & Err.Description
Err.Clear

End Sub

' * * * * End Code * * * *

txtID is the text box bound to the primary key field, so you may need to
change it to suit your form. Next, for the form with the combo box, in
the
combo box's OnNotInList( ) event, paste the following code:

' * * * * Start Code * * * *

Private Sub cboContact_NotInList(NewData As String, Response As Integer)

On Error GoTo ErrHandler

MsgBox "Please double-click this field" & vbCrLf & _
"to add a new entry to the list.", _
vbInformation + vbOKOnly, "No Matching Record"
Response = acDataErrContinue

Exit Sub

ErrHandler:

MsgBox "Error in cboContact_NotInList( ) in " & Me.Name & " form." &
_
vbCrLf & vbCrLf & _
"Error #" & Err.Number & vbCrLf & Err.Description
Err.Clear

End Sub

' * * * * End Code * * * *

cboContact is the name of the combo box containing the list of items
stored
in the table that you want to add a new record to. Next, in the combo
box's
OnDblClick( ) event, paste the following code:

' * * * * Start Code * * * *

Private Sub cboContact_DblClick(Cancel As Integer)

On Error GoTo ErrHandler

Dim nContactID As Long

If (IsNull(Me!cboContact)) Then
Me!cboContact.Text = ""
Else
nContactID = Me!cboContact
Me!cboContact = Null
End If

DoCmd.OpenForm "frmContacts", , , , , acDialog, "New"
Me!cboContact.Requery ' Get all records.

If (nContactID <> 0) Then
Me!cboContact = nContactID ' Reset to original row.
End If

Exit Sub

ErrHandler:

MsgBox "Error in cboContact_DblClick( ) in " & Me.Name & " form." & _
vbCrLf & vbCrLf & _
"Error #" & Err.Number & vbCrLf & Err.Description
Err.Clear

End Sub

' * * * * End Code * * * *

Save both forms and compile the code. Open the form with the combo box
in
Form View and double-click on the combo box to see the other form open,
awaiting a new entry to be added to the original table. Once the new
record
has been typed, close that form to return to the form with the combo box.
The combo box will now have the newest record in the list, ready to be
selected by the user.

HTH.

Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips.

(Please remove ZERO_SPAM from my reply E-mail address, so that a message
will be forwarded to me.)
Beware to those who use munged addresses: known newsgroup E-mail
harvesters
for spammers are (e-mail address removed) and (e-mail address removed)

- - -
When you see correct answers to your question posted in Microsoft's
Online
Community, please sign in to the Community and mark these posts as
"Answers,"
so that all may benefit by filtering on "Answered questions" and quickly
finding the right answers to similar questions. Remember that questions
answered the quickest are often from those who have a history of
rewarding
the contributors who have taken the time to answer questions correctly.
Click to expand...
Click to expand...
 
Back
Top