Populating a field after adding a record to combo box when notonlist

[RuralGuy]

It is actually not even necessary to open the [frmMakeModel Subform]. You can add the record to the table right from the "frmVehicles" as I have done in this version. As for the SubModel, why isn't the ID field in the tblSubmodel an AutoNumber?

 

[pam0566]

I didnt do an autonumber because i have a macro that will add each new transaction as one increased id #.. for example if you were to delete a number, next addition would be last number still in tabel +1, doesnt waste numbers taht may not be kept or used. Macro wont work with an auto number.

 

[pam0566]

wow that is perfect on make model! how can i do the same thing to the submodel field. tried looking in the properties,, but cant find where the CLOSE command is? How can i make it so when you add a submodel it adds it but then automatically closes the form and shows it back on frmvehicles

 

[RuralGuy]

There is no shortage of numbers in the universe and Referential Integrity is difficult to enforce with your method. Just my thoughts.

 

[RuralGuy]

If the [ID] field were an AutoNumber then you could do the same thing I did for the Model and let Access handle the PrimaryKey field. Since it is not then you need to execute code that creates the new [ID] number for you. You did notice that the next form was never even opened right?

 

[pam0566]

ok i can change that submodel field back to autonumber. which code should i copy to do that with ? which event? will it work same as the make model field if i copy and paste. gotta admit getting confused now ....

 

[RuralGuy]

Did you look at the code I put in the cbomodel_NotInList event? Do you understand how it works?

 

[pam0566]

kind of .. my problem is the cbo model has to do with make and model id's and the submodel is simpler and only submodel/submodel id to deal with.. so trying to adapt it, but not sure which part to leave out

 

[pam0566]

this is what you had written on the cbomodel not on list.:
Private Sub cbomodel_NotInList(NewData As String, Response As Integer)
Response = MsgBox("[" & NewData & "] is not recognized in this database." & vbCrLf & vbCrLf & "Would you like to add it?", vbQuestion + vbYesNo, "Unrecognized Data")
If Response = vbYes Then
' DoCmd.OpenForm "frmMakeModel Subform", acNormal, , , acFormAdd, acDialog, _
' Me.cbomake.Column(0) & ";" & NewData
'-- Create a new Supplier record
Dim MySQL As String
MySQL = "Insert Into tblMakeModel(Model,MakeID) " & _
"Values(""" & NewData & """," & Me.cbomake.Column(0) & ")"
CurrentDb.Execute MySQL, dbFailOnError
Response = acDataErrAdded
Else
Response = acDataErrContinue
End If
End Sub

and this is what i am assuming would work on adapting for the simpler submodel field. ..and yes i know about assuming
Private Sub cbosubmodel_NotInList(NewData As String, Response As Integer)
Response = MsgBox("[" & NewData & "] is not recognized in this database." & vbCrLf & vbCrLf & "Would you like to add it?", vbQuestion + vbYesNo, "Unrecognized Data")
If Response = vbYes Then
' DoCmd.OpenForm "frmSubmodel", acNormal, , , acFormAdd, acDialog, _
' Me.cbosubmodel.Column(0) & ";" & NewData
'-- Create a new Supplier record
Dim MySQL As String
MySQL = "Insert Into tblsubmodel " & _
"Values(""" & NewData & """," & Me.cboSubmodel.Column(0) & ")"
CurrentDb.Execute MySQL, dbFailOnError
Response = acDataErrAdded
Else
Response = acDataErrContinue
End If
End Sub

is my thinking anywhere near what needs to happen?

 

[pam0566]

ok i tried it get a run time error 3134

 

[pam0566]

ok changed subform to auto number, do you think i should try the same code i sent you above on notonlist? or did i mess that up when adapting to this field?

 

[pam0566]

Private Sub cboSubmodel_NotInList(NewData As String, Response As Integer)
Response = MsgBox("[" & NewData & "] is not recognized in this database." & vbCrLf & vbCrLf & "Would you like to add it?", vbQuestion + vbYesNo, "Unrecognized Data")
If Response = vbYes Then
' DoCmd.OpenForm "frmSubmodel", acNormal, , , acFormAdd, acDialog, _
' Me.cbosubmodel.Column(0) & ";" & NewData
'-- Create a new Supplier record
Dim MySQL As String
MySQL = "Insert Into tblSubmodel(Submodel) " & _
"Values(""" & NewData & """," & Me.cboSubmodel.Column(0) & ")"
CurrentDb.Execute MySQL, dbFailOnError
Response = acDataErrAdded
Else
Response = acDataErrContinue
End If
End Sub

THIS IS WHAT I HAVE ADAPTED, I THINK THE PROBLEM IS SOMEWHERE IN MY COLUMNS? ERROR SAYS ITS IN INSERT INTO :...

 

[RuralGuy]

If you have changed the [ID] field to an AutoNumber then it need not be specified but you *do* need to tell the SQL what field you want the data in. BTW, putting your code inside if {code}{/code} tags - using brackets instead of braces - will preserve the formatting and make it easier to read. It is the "#" button on the menu. Take another look at the SQL in the cbomodel event. Post back if you are still having trouble.

 

[pam0566]

I am trying to do that. i see the MySQL = Insert Into TblMakeModel(Model,MakeID) "& _
How should i adapt that for the submodel though? since it is only relying on info from 1 table not 2.. that is where i think i am wrong.. and the next line beginnin with "Values.... where it gets to Me.cbomake.Column(0)/ should that be Me.cbosubmodel and Column(0) or (1)
sorry to be a pain, but while i get what was done for the models, for some reason i am making the simpler submodel more complicated than it is ....

 

[RuralGuy]

We are going to have to take this one step at a time. Your TblMakeModel table has three fields but I only specified two of them with:
"MySQL = Insert Into TblMakeModel(Model,MakeID) "
...do you know why?

 

[pam0566]

to pull up the model that corresponds to that make ID.?

 

[RuralGuy]

The following code:

Dim MySQL As String
MySQL = "Insert Into tblMakeModel(Model,MakeID) " & _
"Values(""" & NewData & """," & Me.cbomake.Column(0) & ")"
CurrentDb.Execute MySQL, dbFailOnError

actually adds a record to the tblMakeModel table and fills in the Model and MakeID fields of that table with: NewData and the value from Me.cbomake.Column(0) respectively. The NewData value is what the user keyed in to the ComboBox that didn't match anything in the lookup table and do you know what Me.cbomake.Column(0) contains?

 

[pam0566]

no, to be really honest, the me. commands i copy and dont understand exactly what they stand for. i would assume something about matching the cbomake ID /column 0 to the make we selected from cbomake?

By the way sorry if i sound like an idiot.. i really appreciate your help in trying to understand it.

 

[RuralGuy]

I'm not sure how much value other are getting out of this thread. Maybe we should consider taking this off line and using an IM to communicate. I have a little time if you want to do that.

 

[pam0566]

ok how do we go offline. i have msn ims/ and aol ims. and think yahoo thru my msn ims