VBA or module help 1

[ragnarok75]

I need either code or a module to perform the following:

I have a form with a listbox that is filtered by region. Upon making multiple selections in a listbox and clicking a button I need an autonumber assigned from tableA and then that number and two fields from each listbox selection to make up new records in another table. Each record selected in the listbox will become a new record in TableB with the autonumber assigned from tableA. Is there a way to do this?

 

[Zion7]

How does one assign an AUTONUMBER???

...but using an "Insert" statement, the rest of your code sounds feasible.

 

[ragnarok75]

I have the autonumber handled, but I need to take values off of a form and insert them into a table. Example: Click on a create button on formA - opens formB and populates some values, also needs to take 3 fields off of formB and insert them into TableA - What would the Insert Into syntax be? Should I run it from DoCmd.RunSql or is there a better way?

 

[Zion7]

Docmd.RunSQL is fine.

You can find the syntax for an SQL Insert statement, in VBA help.

to add variables to the statement, you concatenate.

strSQL = "INSERT INTO tblCountry(...." & _
"VALUES('" & Me.txtCountry & "'," & _
Me.txtSize & ",'" & Me.txtCapital & "',....)

 

[ragnarok75]

OK - I have the insert into working, but here is my problem. One of the values is text which can be made up of either letters and numbers or just numbers. I cannot get both to work. The numbers work with no quotes and the letters and numbers work with single quotes. How do I get both to work. Also how do I select all values in a listbox on load? Or how do I cycle through all records in a query in VBA?

 

[Zion7]

To prepare for both data ttypes, one way;

On error goto xxx
Dim varValue as Variant

varValue = CInt(Me.txtData)

strSQL = "INSERT INTO tblCountry(...." & _
"VALUES('" & Me.txtCountry & "'," & _
varValue & ",'" & Me.txtCapital & "',....)"


xx:
Exit Sub
xxx:
If err = 13 Then 'datatype mismatch(i believe)
varValue = chr(34) & Me.txtDate & chr(34)
Resume next
Else
msgBox err & vbCrLf & error$
End If
End Sub

 

[ragnarok75]

I get an overflow error when trying above suggestion.

 

[Zion7]

replace err = 13, with overflow number????

let's see your code?

 

[ragnarok75]

With the below code I get an error 94 - invalid us of null.

Private Sub cmdCrtRte_click()

On Error GoTo ERROR
Dim strInsSQL As String
Dim lb2varItem As Variant
Dim varValue As Variant

varValue = CInt(lbqryFiltSF.Column(0))

strInsSQL = "INSERT INTO JunctionTP(TripNum, PONum, VendorID)" & _
            "VALUES(" & Me.TripNum & "," & _
            varValue & "," & _
            lbqryFiltSF.Column(1) & ")"

With lbqryFiltSF
    For Each lb2varItem In .ItemsSelected
        If Not IsNull(lb2varItem) Then
            DoCmd.RunSQL strInsSQL
        End If
    Next
End With


DoCmd.OpenForm "TripCreate", , , "[Trip.TripNum]=" & Me.TripNum.Value

ERROR:
    If Err = 6 Then
        varValue = Chr(34) & lbqryFiltSF.Column(0) & Chr(34)
        Resume Next
    Else
        MsgBox Err & vbCeLf & ERROR$
    End If
End Sub

 

[Zion7]

which line?

BTW, you don't have Exit Sub, before your error call???
check-out line feed, on your error message.

 

[ragnarok75]

I got this working - Thanks for all your help!