Solved Insert Into Query Problem (1 Viewer)

silentwolf

Active member
Local time
Today, 06:49
Joined
Jun 12, 2009
Messages
545
Hi guys,

In my main form I have a few subforms. On one subform I have a button to open another form to search for ITEMS.

All works not a problem.

However what I would like to achive is to insert the ID from the Subform and the ID from the Searchform after selecting a ITEM into a table.


here is what I have so far:

Code:
Private Sub btnOK_Click()

    Dim lngArtID As Long
    lngArtID = Me.Form.Art_ID
    
    Dim lngModID As Long
    lngModID = Forms![frm_Auftraege]![sfm_AuftragModelle].Form![AufM_ID]
    
    Call InsertInto(lngArtID, lngModID)
    
End Sub

Code:
Sub InsertInto(lngModID As Long, lngArtID As Long)
 
    Dim dbs As Database
    
    Set dbs = CurrentDb
    
    dbs.Execute " INSERT INTO tbl_ArtikelModell " _
        & "(ArtM_Art_IDRef,ArtM_Mod_IDRef) VALUES " _
        & "(lngArtID, lngModID);"
        
    dbs.Close
 
End Sub


I do gen an Error Amount of Parameters do not match Runtime Error "3061"

But I can not spot it why I get this error?

the tbl_ArtikelModell has an ID .. AutoNumber and those two Fields as mentioned above.

Also I am not sure how to have a Error Handler to that code as I have set up an INDIZIS to that table so there are no dublicates allowed.

Would be nice if someone can give me a little hint here.

Many Thanks

Albert
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 09:49
Joined
Feb 19, 2002
Messages
42,981
Code:
dim strSQL AS String

....

strSQL = " INSERT INTO tbl_ArtikelModell " _
        & "(ArtM_Art_IDRef,ArtM_Mod_IDRef) VALUES " _
        & "(lngArtID, lngModID);"

Then change the execute to execute strSQL but put a stop on that line so you can print out the actual string that is being sent to the server.
 

silentwolf

Active member
Local time
Today, 06:49
Joined
Jun 12, 2009
Messages
545
Hi Pat,

thanks for your reply!

I did change it like that

Code:
    Dim dbs As Database
    
    Set dbs = CurrentDb
    
    Dim strSQL As String
    
    strSQL = " INSERT INTO tbl_ArtikelModell " _
        & "(ArtM_Art_IDRef,ArtM_Mod_IDRef) VALUES " _
        & "(lngArtID, lngModID);"
        
    dbs.Execute strSQL, dbFailOnError

still getting the Error of not correct Parameters ?

What am I doing wrong here?

Do I need to ID field too? for the AutoField?
 

pbaldy

Wino Moderator
Staff member
Local time
Today, 06:49
Joined
Aug 30, 2003
Messages
36,118
You need to concatenate the variables into the string. If both are numeric data types:

& "(" & lngArtID & ", " & lngModID & ");"
 

silentwolf

Active member
Local time
Today, 06:49
Joined
Jun 12, 2009
Messages
545
Hi pbaldy,

many thanks to you yes it works now!!!

Fantastic!!!

Cheers
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 09:49
Joined
Feb 19, 2002
Messages
42,981
@silentwolf The point of my suggestion was not to fix the problem but to give you a way to identify the problem. You needed to put the stop in the code and print out the string to the debug window. Then post THAT version of the query.

I'm glad that Paul pointed out the problem for you.

There is no advantage to embedding static SQL in VBA. Queries are easier to test and code if you use the QBE. You can reference form fields easily and you even get intellisense.
 

silentwolf

Active member
Local time
Today, 06:49
Joined
Jun 12, 2009
Messages
545
@Pat,

no problem Pat thanks for the advice I do mix it a little in my code, I do use queries too sometimes and make use of the QBE.
Not always finding the best solution I guess but I am trying to get better in it and with all of you expert advice and beeing able to put more time into it I hope I will further progress.

Many thanks for all and every input I am getting from everyone.
 

Users who are viewing this thread

Top Bottom