Updating combo box from another form

  • Thread starter Thread starter Billy B
  • Start date Start date
B

Billy B

I have two forms: frmPlantsAdd and frmNewStore. The first form has a combo
box (cboListRetailer) that has a NotInList event. When this runs, it opens
the second form and upon saving the new record I want to update the StoreId
Storename in the first form. Below is the code I currently have and the
lines of bad code is identified. I have tried a form requery from the second
form on the first form and that didn't work as the NotInList procedure tried
to run again. What do I need to do?

Private Sub cboListRetailer_NotInList(NewData As String, Response As Integer)
Dim IntNew As Integer
Dim intNewID As Integer
intNewID = DMax("StoreID", "tblStore")
intNewID = intNewID + 1
IntNew = MsgBox("Do you want to add a new vendor?", vbYesNo)
If IntNew = vbYes Then
'RunCommand acCmdUndo
Response = acDataErrContinue
Dim strCheck As String
strCheck = Me.cboListRetailer.Text

DoCmd.OpenForm FormName:="frmNewStore", datamode:=acFormAdd
Forms!frmnewstore!StoreID = intNewID
Forms!frmnewstore!StoreName = strCheck
Forms!frmnewstore.cmdCancelNewStore.Visible = False
Forms!frmnewstore.Address.SetFocus
Else
MsgBox "The store name you entered isn't being stored as a new record"
RunCommand acCmdUndo
Response = acDataErrContinue
End If
End Sub

Private Sub cmdSaveStoreInfo_Click()
'Purpose: Create a new store record
'Created: 12/2/07
'Modified:
'Created by: Wallace Fisher

Dim strAsk As String, intNewNum As Integer, intOldNum As Integer
'set and open connections and recordset to tblStore
'OpenDbConnection through OpenDbConnction function

Set cnn = OpenDbConnection()

'Open the recordset associated with store table
Set rsStore = CreateStoreRecordset(cnn)

With rsStore
.AddNew
.Fields("StoreID").Value = Me.StoreID
.Fields("Storename").Value = Me.StoreName
.Fields("Address").Value = Me.Address
.Fields("City").Value = Me.City
.Fields("State").Value = Me.State
.Fields("ZipCode").Value = Me.ZipCode
.Fields("Phone").Value = Me.Phone
.Fields("Fax").Value = Me.Fax
.Fields("Email").Value = Me.Email
.Update
End With

'If this form is being opened via frmPlantsAdd
'want to update the StoreID and StoreName in the
'frmPlantsAdd forms combo box (cboListRetailer)
Dim fIsOpen As Boolean
fIsOpen = CurrentProject.AllForms("frmPlantsAdd").IsLoaded
If fIsOpen = True Then

'************************************ errors on the following two lines

Forms!frmPlantsAdd!cboListRetailer.Column(0).Value = Me.StoreID.Value
Forms!frmPlantsAdd!cboListRetailer.Column(1).Value =
Me.StoreName.Value
Forms!frmPlantsAdd!lblStoreInfo.Caption = Me.StoreName _
& vbNewLine & Me.Address _
& vbNewLine & Me.City & " " & Me.State _
& " " & Me.ZipCode & vbNewLine & Me.Phone
Forms!frmPlantsAdd.Requery
DoCmd.Close acForm, Me.Name
Exit Sub
Else..........
 
I have two forms: frmPlantsAdd and frmNewStore. The first form has a combo
box (cboListRetailer) that has a NotInList event. When this runs, it opens
the second form and upon saving the new record I want to update the StoreId
Storename in the first form. Below is the code I currently have and the
lines of bad code is identified. I have tried a form requery from the second
form on the first form and that didn't work as the NotInList procedure tried
to run again. What do I need to do?

Private Sub cboListRetailer_NotInList(NewData As String, Response As Integer)
Dim IntNew As Integer
Dim intNewID As Integer
intNewID = DMax("StoreID", "tblStore")
intNewID = intNewID + 1
IntNew = MsgBox("Do you want to add a new vendor?", vbYesNo)
If IntNew = vbYes Then
'RunCommand acCmdUndo
Response = acDataErrContinue
Dim strCheck As String
strCheck = Me.cboListRetailer.Text

DoCmd.OpenForm FormName:="frmNewStore", datamode:=acFormAdd
Forms!frmnewstore!StoreID = intNewID
Forms!frmnewstore!StoreName = strCheck
Forms!frmnewstore.cmdCancelNewStore.Visible = False
Forms!frmnewstore.Address.SetFocus
Else
MsgBox "The store name you entered isn't being stored as a new record"
RunCommand acCmdUndo
Response = acDataErrContinue
End If
End Sub

Private Sub cmdSaveStoreInfo_Click()
'Purpose: Create a new store record
'Created: 12/2/07
'Modified:
'Created by: Wallace Fisher

Dim strAsk As String, intNewNum As Integer, intOldNum As Integer
'set and open connections and recordset to tblStore
'OpenDbConnection through OpenDbConnction function

Set cnn = OpenDbConnection()

'Open the recordset associated with store table
Set rsStore = CreateStoreRecordset(cnn)

With rsStore
.AddNew
.Fields("StoreID").Value = Me.StoreID
.Fields("Storename").Value = Me.StoreName
.Fields("Address").Value = Me.Address
.Fields("City").Value = Me.City
.Fields("State").Value = Me.State
.Fields("ZipCode").Value = Me.ZipCode
.Fields("Phone").Value = Me.Phone
.Fields("Fax").Value = Me.Fax
.Fields("Email").Value = Me.Email
.Update
End With

'If this form is being opened via frmPlantsAdd
'want to update the StoreID and StoreName in the
'frmPlantsAdd forms combo box (cboListRetailer)
Dim fIsOpen As Boolean
fIsOpen = CurrentProject.AllForms("frmPlantsAdd").IsLoaded
If fIsOpen = True Then

'************************************ errors on the following two lines

Forms!frmPlantsAdd!cboListRetailer.Column(0).Value = Me.StoreID.Value
Forms!frmPlantsAdd!cboListRetailer.Column(1).Value =
Me.StoreName.Value
Forms!frmPlantsAdd!lblStoreInfo.Caption = Me.StoreName _
& vbNewLine & Me.Address _
& vbNewLine & Me.City & " " & Me.State _
& " " & Me.ZipCode & vbNewLine & Me.Phone
Forms!frmPlantsAdd.Requery
DoCmd.Close acForm, Me.Name
Exit Sub
Else..........
Click to expand...

I think this is failing because the new vendor isn't in your combo box
Forms!frmPlantsAdd!cboListRetailer.Column(1).Value =
Click to expand...

....is redundant - just use Forms!frmPlantsAdd!cboListRetailer =
storeID and that will set the value.

Hope the helps,
James
 
I tried that and get an error message that the record must be saved before
requery. Any ideas?

I have also found information about acDataErrAdded but cannot get it to
update from the frmNewStore procedure.

Thanks
 
I tried that and get an error message that the record must be saved before
requery. Any ideas?

I have also found information about acDataErrAdded but cannot get it to
update from the frmNewStore procedure.

Thanks
Click to expand...

Try saving the record before moving to the second form (the cheap way
to do this is set Me.Dirty=False) then the form will be saved and you
shouldn't get this error.

-- James
 
Back
Top