Dynamic ComboBox Not in List Code Error

[quicova]

Hello Everyone.



I have a error that I have tried everything to fix and can't seem to find the answer.

I have 2 combobox, when you select a value in the first box it changes the values in the other box. All this is working.
I achieved this by creating a query and having the second combobox grab its data from the query.

I want to be able to add new values to the combobox just by typing on them, so I created a Not in List event. for each box.
For the first box it is working, but for the second box I get an error saying:

"Number of query values and destination fields are not the same"

for the second one, I need to add to the table not only the new value but also the PK from the first ComboBox

here is the code for the second box not in list event:

Private Sub Add_Weight_Location_NotInList(NewData As String, Response As Integer)
    
    Answer = MsgBox("add " & NewData & " as a new Location?", vbYesNo, "Location doesn't exist")
    If Answer = vbYes Then
        SQLStmt = "Insert into Weight_Locations(Locations, CategoryID) " & _
        " VALUES (" & NewData & " & '" & Me.Add_Weight_cat & "')"
        
        DoCmd.SetWarnings False
        
        DoCmd.RunSQL SQLStmt
        Response = acDataErrAdded
        
        DoCmd.SetWarnings True
    
        
    Else
        Response = acDataErrContinue
    End If
End Sub

I also attached the file
Hope someone can help me out with this, I have been around this one for hours now.

Thanks so much

[pbaldy]

I'm not on a computer right now so can't look at the sample, but see if the SQL is coming out as you expect:

http://www.baldyweb.com/ImmediateWindow.htm

[quicova]

I checked it and couldn't figure out the problem. I don't think the problem is with the SQL.

I think i need to requery the query or something.
I think the problem is the combobox takes its data from the query, but the not in list event add the data to the table.
So I think i need to requery the query to the new data so it shows up in the combobox.

But I don't know how to go about doing the requery on a query

or maybe I'm totally wrong... I don't know
I'm completely lost at the moment

[pbaldy]

I just tried what I suggested you try, and the error is pretty obvious (and it is with the SQL).

Edit: Actually there are 2 errors, both with the SQL.

[quicova]

Ok I tried again what you said and I think I found the problem in the SQL.

it was returning this:

Insert into Weight_Locations(Locations, CategoryID) VALUES (test & '1')

so I change the SQL to:

SQLStmt = "Insert into Weight_Locations(Locations, CategoryID) " & _
        " VALUES (" & NewData & ", " & Me.Add_Weight_cat & ")"

and now is returning:

Insert into Weight_Locations(Locations, CategoryID) VALUES (test, 1)

which is better I think

However I still get an error. now I get a pop up window called Test asking me to enter parameter value

and what ever I type in this window is what is added to the table with the right CategoryID from Me.Add_Weight_cat
Any ideas on how to fix this?

I would really appreciate someones help on this.
Thanks so much

[pbaldy]

The second error you made wasn't having the single quotes around one of the fields, it was having them around the wrong field. Text data types require them.

[quicova]

You where absolutely right.
It worked thanks so much

this was the first time I ever used SQL. I have no idea what I'm doing, I was just copying what I can find online.
This will totally change by database. I'm going to replace all the append and update query with this.

Thanks again.

[pbaldy]

Happy to help!