Inserting data from textboxes to table

[OrganMan]

Wondering if someone can help me out. Upon click of an Add Card To Collection button I would like to save the values of two textboxes into the fields of a table. First of all the textboxes are filled when a record in a listbox is clicked through the control source property, and I would like to save the values the user enters into txtCondition and txtQuantity into a table named Collection with fields CardId, CardNum, Condition, Quantity. CardId is the primary key, however CardNum can be the same for different cards. CardNum would have to be inserted into the table as well, it was selected from my Cards table with a recordset. Not sure how too code with my primary key. I know this sounds kind of confusing but can some lead me in the right direction?

Thanks for the help

OM

 

[ezfriend]

Is the CardID an auto number? If so, you shouldn't need to.

 

[OrganMan]

To break down my task, can someone tell me what is wrong with my code:

Private Sub cmdAddCardToCollection_Click()
DoCmd.RunSQL "INSERT INTO Collection ([CardId],[CardNo],[[ConditionId],[Quantity])"
SELECT Me.txtCardId.Text, Me.txtCardNo.Text, Me.cboCondition.Value, Me.txtQuantity.Text; FROM frmTabCards

End Sub

I am looking to add to the collection table the data that the user adds to the textbox Quantity on the form as well as selects from the combobox Condition, CardNo is automatically filled in txtCardNo when clicking on the card from my listbox.

Maybe there is another approach here, I have a Cards table that holds different data regarding an individual card, example, CardId which is AutoNum, CardId which is number because Card # 1 can be of a different player from one set of cards to the next, PlayerFirstName, PlayerLastName, etc.

I have also created a separate table named Collection which has the following fields: CardId (Number), CardNo (Number), Condition, (text) Quantity, since I can have multiple copies of a unique card in different conditions.

Thanks

 

[aman]

Try the following code

dim strSQL as String
strSQL= "INSERT INTO Collection ([CardId],[CardNo],[[ConditionId],[Quantity]) SELECT Me.txtCardId.Text, Me.txtCardNo.Text, Me.cboCondition.Value, Me.txtQuantity.Text FROM frmTabCards"
docmd.runsql strSQL

Cheers
Aman

 

[OrganMan]

I now get the following error:

Runtime error: 3078
The Micosoft Jet Database Engine cannot find the input table or query 'frmtabCards'. make sure it exists and that its name is spelled correctly. I do not have a query for anything here. I only have frmtabCards in the FROM clause of the SELECT statement because this is where the textboxes\comboboxes are located.

 

[ezfriend]

This is my normal way of doing things. Hopefully it will ease some pains.
Make sure to have some error handling for this.

Private Sub cmdAddCardToCollection_Click()
    
    Dim sSQL As String
    
    sSQL = ""
    sSQL = sSQL & "INSERT INTO Collection ([CardId],[CardNo],[[ConditionId],[Quantity]) "
    sSQL = sSQL & "VALUES("
    sSQL = sSQL & "'" & DUSQ(Me.txtCardId & "") & "', "
    sSQL = sSQL & "'" & DUSQ(Me.txtCardNo & "") & "', "
    sSQL = sSQL & Me.cboCondition & ", "
    sSQL = sSQL & Me.txtQuantity & ") "
    
    CurrentDb.Execute sSQL
    
End Sub

Public Function DUSQ(ByVal sValue As String)
    DUSQ = Replace(sValue, "'", "''")
End Function