Forms are fun (except when they need combo box VBA code I don't kn

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

Guest

Got a combo box that's used to select records. That works just fine.
Problem is that if I change records with the navigation button, that doesn't
update the combo box. Also, is it possible for the user to be able to add a
record if the name doesn't appear in the combo box. Oh, and I realize I'm
way out of my league here, so thanks for your patience.
 
Since the combo box is not bound, it will now follow your record navigation.
Most likely, you have a text box on the form that is the same value you would
want yout combo box to show. If this is the case, you can set the Control
Source property for the combo to the text box:

=[MyTextBox]
(MyTextBox would be the name of the text box that is the same value you want
to show in the text box)

Now, to add a new entery, you will want to use the Not In List event. This
site has a good explanation of this event and how to use it
http://www.pacificdb.com.au/MVP/Code/NIL.htm
 
Klatuu said:
Since the combo box is not bound, it will now follow your record navigation.
Most likely, you have a text box on the form that is the same value you would
want yout combo box to show. If this is the case, you can set the Control
Source property for the combo to the text box:

=[MyTextBox]
(MyTextBox would be the name of the text box that is the same value you want
to show in the text box)
Click to expand...
When I try to do this, it says I can't change the value because it's bound,
so the VBA code doesn't execute to find the right record.
Now, to add a new entery, you will want to use the Not In List event. This
site has a good explanation of this event and how to use it
http://www.pacificdb.com.au/MVP/Code/NIL.htm
Click to expand...

From that site I got the following code and customized it for my
circumstances.
-----------
Private Sub AgentName_NotInList(NewData As String, Response As Integer)

Dim db As Database
Set db = CurrentDb

'Add the new value to the field
db.Execute "INSERT INTO tblTotalAgentListABCD (LastName) VALUES (""" &
NewData & """)", dbFailOnError

'Tell Access you've added the value
Response = acDataErrAdded

db.Close
Set db = Nothing

End Sub
 
If you use a bound combo box, there are some additional issues that need to
be addressed. Here is the problem. When you enter a new value in a combo
box, you have changed the value in the bound field so now the record is
Dirty. Next, you try to add a new record using the new value, but before
that can happen, it updates the current record with the new value, so you
will get an error trying to add a duplicate record.
There are a couple of ways you can address this. One is to have your combo
as an unbound control and a hidden text box that is the bound control. Now
when you change the value in the combo, you are not affecting the bound value
and can add a new record without affecting the current record. The only
thing you need to do is update the hidden text box with the value of the
combo in the combo's After Update event.
The other way involves replacing the combo box's value with its OldValue
before trying to create a new record. Here is an example of that technique:

Private Sub cboActivity_NotInList(NewData As String, Response As Integer)
Dim rst As Recordset

If MsgBox(NewData & " Is Not In The Attribute Table " & vbNewLine _
& "Do you want to add it", _
vbInformation + vbYesNo, "Not Found") = vbYes Then
Me.cboActivity = Me.cboActivity.OldValue
CurrentDb.Execute ("INSERT INTO CISAttributeTable (ACTIVITY) " _
& "VALUES ('" & NewData & "');"), dbFailOnError
Me.Requery
Set rst = Me.RecordsetClone
rst.FindFirst "[Activity] = '" & NewData & "'"
Me.Bookmark = rst.Bookmark
Set rst = Nothing
Response = acDataErrAdded
Else
Me.cboActivity.Undo
Response = acDataErrContinue
End If

End Sub

As to the SQL problem, I think you might be missing one quote mark, but I am
not sure.

db.Execute "INSERT INTO tblTotalAgentListABCD (LastName) VALUES (""" &
NewData & """")", dbFailOnError

Gnowor said:
Klatuu said:
Since the combo box is not bound, it will now follow your record navigation.
Most likely, you have a text box on the form that is the same value you would
want yout combo box to show. If this is the case, you can set the Control
Source property for the combo to the text box:

=[MyTextBox]
(MyTextBox would be the name of the text box that is the same value you want
to show in the text box)
Click to expand...
When I try to do this, it says I can't change the value because it's bound,
so the VBA code doesn't execute to find the right record.
Now, to add a new entery, you will want to use the Not In List event. This
site has a good explanation of this event and how to use it
http://www.pacificdb.com.au/MVP/Code/NIL.htm
Click to expand...

From that site I got the following code and customized it for my
circumstances.
-----------
Private Sub AgentName_NotInList(NewData As String, Response As Integer)

Dim db As Database
Set db = CurrentDb

'Add the new value to the field
db.Execute "INSERT INTO tblTotalAgentListABCD (LastName) VALUES (""" &
NewData & """)", dbFailOnError

'Tell Access you've added the value
Response = acDataErrAdded

db.Close
Set db = Nothing

End Sub
-----------------------
And then it doesn't pick up the name of my table (table name is
TotalAgentListABCD, and field name is LastName) Thanks for the quick
response,
Klatuu.
Click to expand...
 
Good news bad news situation here.

Got the NotInList event to add a record to the table (I had the table name
wrong), problem is, that after the record's added, I get an error that it
can't find the record. What I think is happening is that it's loading the
list for the combo box when I open the form, but I need it to reload that
list when the new record is added, and then repeat the find I have in my
AfterUpdate Sub to select the new record it just added. Again, I think I'm
starting to get a grasp of what i need to do, the code is just eluding me at
the moment. I'm also probably adding way mroe steps than neccessary.

BTW, Klatuu you are frigging awesome for helping me through this. If you're
ever in the San Fran Bay Area, I owe you big for all the help. AIM=gnowor if
you've got the time.
-------------
Private Sub AgentName_AfterUpdate()
' Find the record that matches the control.
Dim rs As Object

Set rs = Me.Recordset.Clone
rs.FindFirst "[LastName] = '" & Me![AgentName] & "'"
If Not rs.EOF Then Me.Bookmark = rs.Bookmark
End Sub


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

Dim db As Database
Dim rs As Object
Set db = CurrentDb

'Add the new value to the field
db.Execute "INSERT INTO TotalAgentListABCD (LastName) VALUES (""" & NewData
& """)", dbFailOnError

'Tell Access you've added the value
Response = acDataErrAdded

rs.FindFirst "[LastName] = '" & Me![AgentName] & "'"

db.Close
Set db = Nothing

End Sub
 
It can't find it because the form's recordset has to be required. You put it
in the table, but it is not in the recordset. A form's recordset only
contains the records that were in the table when the form was loaded or last
requried.

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

Dim db As Database
Dim rs As Object
Set db = CurrentDb

'Add the new value to the field
db.Execute "INSERT INTO TotalAgentListABCD (LastName) VALUES (""" & NewData
& """)", dbFailOnError

'Tell Access you've added the value
Response = acDataErrAdded
Me.Requery
rs.FindFirst "[LastName] = '" & Me![AgentName] & "'"

db.Close
Set db = Nothing
 
Back
Top