Insert Into still not working

[Guest]

Hi there,

I have a form with a list box (lstApps). What I
need to do is have EmpId and Machine (2 text boxes) and everything in the
list box inserted into the tblGetApps table with Empid and Machine being
repeated in each record.

Private Sub cmdAdd_Click()
Dim db As Database

Set db = CurrentDb

db.Execute "INSERT INTO tblGetApps ( EMPLID, Machine, Apps ) & _
"Values (EmplId, Machine, lstApps)"

set db = Nothing

I get a syntax error and am not sure what is wrong with my query formation.

I have also tried:

Private Sub cmdAdd_Click()
Dim db As Database
' Dim rs As Recordset
Dim App as String

Set db = CurrentDb



' Set rs = db.OpenRecordset("tblGetApps")
' App = Me.lstApps.Value

'

'
' rs.AddNew
' rs![EmplId] = EmplId
' rs![Machine] = Machine
' rs![Apps] = App
' rs.Update
'


rs.Close: Set rs = Nothing
Set db = Nothing
End Sub

This works, sort of but will only insert the one item I have clicked on in
the list box, otherwise it just inserts the two items in the text boxes and
leaves Apps blank. What can I do to fix this so that everything from the
list box gets inserted?

Thanks,

All help immensely appreciated!

 

[Brian Bastl]

Your values to be inserted need to be concatenated to your SQL.

On the Assumption that EmplId is numeric, Machine is text, and lstApps is
numeric, your code would look like:

Dim db As DatabaseSet db = CurrentDb

db.Execute "INSERT INTO tblGetApps ( EMPLID, Machine, Apps ) & _
"Values (" & EmplId & ",'" & Machine "', " & lstApps & ")",
dbFailOnError

HTH,
Brian

Hi there,

I have a form with a list box (lstApps). What I
need to do is have EmpId and Machine (2 text boxes) and everything in the
list box inserted into the tblGetApps table with Empid and Machine being
repeated in each record.

Private Sub cmdAdd_Click()
Dim db As Database

Set db = CurrentDb

db.Execute "INSERT INTO tblGetApps ( EMPLID, Machine, Apps ) & _
"Values (EmplId, Machine, lstApps)"

set db = Nothing

I get a syntax error and am not sure what is wrong with my query formation.

I have also tried:

Private Sub cmdAdd_Click()
Dim db As Database
' Dim rs As Recordset
Dim App as String

Set db = CurrentDb



' Set rs = db.OpenRecordset("tblGetApps")
' App = Me.lstApps.Value

'

'
' rs.AddNew
' rs![EmplId] = EmplId
' rs![Machine] = Machine
' rs![Apps] = App
' rs.Update
'


rs.Close: Set rs = Nothing
Set db = Nothing
End Sub

This works, sort of but will only insert the one item I have clicked on in
the list box, otherwise it just inserts the two items in the text boxes and
leaves Apps blank. What can I do to fix this so that everything from the
list box gets inserted?

Thanks,

All help immensely appreciated!

 

[Brian Bastl]

Sorry, just noticed that I left out the trailing ampersand. Should be: '" &
Machine & "'

Brian

Your values to be inserted need to be concatenated to your SQL.

On the Assumption that EmplId is numeric, Machine is text, and lstApps is
numeric, your code would look like:

Dim db As DatabaseSet db = CurrentDb

db.Execute "INSERT INTO tblGetApps ( EMPLID, Machine, Apps ) & _
"Values (" & EmplId & ",'" & Machine "', " & lstApps & ")",
dbFailOnError

HTH,
Brian

Hi there,

I have a form with a list box (lstApps). What I
need to do is have EmpId and Machine (2 text boxes) and everything in the
list box inserted into the tblGetApps table with Empid and Machine being
repeated in each record.

Private Sub cmdAdd_Click()
Dim db As Database

Set db = CurrentDb

db.Execute "INSERT INTO tblGetApps ( EMPLID, Machine, Apps ) & _
"Values (EmplId, Machine, lstApps)"

set db = Nothing

I get a syntax error and am not sure what is wrong with my query formation.

I have also tried:

Private Sub cmdAdd_Click()
Dim db As Database
' Dim rs As Recordset
Dim App as String

Set db = CurrentDb



' Set rs = db.OpenRecordset("tblGetApps")
' App = Me.lstApps.Value

'

'
' rs.AddNew
' rs![EmplId] = EmplId
' rs![Machine] = Machine
' rs![Apps] = App
' rs.Update
'


rs.Close: Set rs = Nothing
Set db = Nothing
End Sub

This works, sort of but will only insert the one item I have clicked on in
the list box, otherwise it just inserts the two items in the text boxes and
leaves Apps blank. What can I do to fix this so that everything from the
list box gets inserted?

Thanks,

All help immensely appreciated!