Prefilling a combo box with data from another form

S

samaclau

I make the user select a value from a combo box. However, if the item is not
available, I have an Add button next to the combo box that executes a macro
that: brings up another form so that the user can add a new record; and then
do a requery so that the new record is available on that original form's
combo box. Works great. But now I would like that new record to be
automatically filled in the combo box. I do not know how to pass the data
from the called form back to the original form. Please tell me the best way
to do this. I would prefer to not use visual basic if I can avoid it.
Thanks.
 
K

Klatuu

You should not be avoiding VBA if you want to develop Access applications.
You should be learning it.
Macros have their limitations. You are up against one.
 
S

samaclau

Since the new record's key added to the table is an autonum, I tried to use
the DMAX function in the macro, but keep getting an error. If I must use VBA
how do I do what I need to do?
 
K

Klatuu

You can put the value in the first form's combo from the AddNew form after
the requery. Since I don't know your naming, I can only give an example, but
it would be something like:

Forms!FirstFormName!ComboBoxName = Me.ControlWithKeyValue
 
S

samaclau

Were does this line go? Thanks.

Klatuu said:
You can put the value in the first form's combo from the AddNew form after
the requery. Since I don't know your naming, I can only give an example, but
it would be something like:

Forms!FirstFormName!ComboBoxName = Me.ControlWithKeyValue
Click to expand...
 
S

samaclau

Thanks. That solved my problem. However, the addnew form is not always
called from the first form. How do I check if the first form is loaded?
 
S

samaclau

I copied the code for the isloaded function from the Northwind sample
database. Works fine now. Thanks again!
 
K

Klatuu

If CurrentProject.AllForms("FirtForm").IsLoaded Then
Forms!FirstFormName!ComboBoxName = Me.ControlWithKeyValue
End If
 
C

CW

I have been trying to work out how to do this and would be really grateful if
you could explain please!
I'm not that concerned about the refinement that has just been added with
help from Klatuu, but I can follow that and add it on if it turns out to be
necessary.
I'm just after the underlying bit for adding a new record to a table so that
it will become accessible in a combo.
Thanks a lot
CW
 
S

samaclau

1. Create a form to enter the new record
2. Create a macro:
Macro Name Action
--------------- --------
AddRec : On Click OpenForm (make the record option New)
Requery (the combo box name)
3. Create a label on the form by the combo box.
4. Under the Properties/Event/On Click enter a macro AddRec

Hope this helps.
 
K

Klatuu

Here is some sample code from one of my applications that does that. For
this to work, you must set the Limit To List property of the combo box to Yes:

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
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
 
C

CW

Thanks a lot! I'll give that a try.

samaclau said:
1. Create a form to enter the new record
2. Create a macro:
Macro Name Action
--------------- --------
AddRec : On Click OpenForm (make the record option New)
Requery (the combo box name)
3. Create a label on the form by the combo box.
4. Under the Properties/Event/On Click enter a macro AddRec

Hope this helps.
Click to expand...
 
C

CW

That's good, I think I can follow it - but how does it get triggered? Do I
need something in one of the Events for the combo, or an additional command
button beside the combo that will fire this up, or ...?
Thanks
CW

Klatuu said:
Here is some sample code from one of my applications that does that. For
this to work, you must set the Limit To List property of the combo box to Yes:

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
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


--
Dave Hargis, Microsoft Access MVP


CW said:
I have been trying to work out how to do this and would be really grateful if
you could explain please!
I'm not that concerned about the refinement that has just been added with
help from Klatuu, but I can follow that and add it on if it turns out to be
necessary.
I'm just after the underlying bit for adding a new record to a table so that
it will become accessible in a combo.
Thanks a lot
CW
Click to expand...
Click to expand...
 
Top