Insert Into Help (1 Viewer)

ria4life · Dec 7, 2018

I have the below sql statement which works fine...Id like to expand it so i can copy multiple values from the form into the same table.
EG: id like to copy values from sig2, sig3 sig4 etc..text92 would remain unchanged.

PHP:

Private Sub CmdSave_Click()
If Me.CmdSave.Visible = "True" Then

sql = "INSERT INTO TBL_Pick_Selection([Employee_ID],[Date_Selected])" _
    & "VALUES ('" & Me.Text92 & "','" & Me.Sig1 & "')"

CurrentDb.Execute sql
Else
End If
End Sub

plog · Dec 7, 2018

So what have you tried? Getting any errors? I mean, you just follow the pattern you have going.

ria4life · Dec 7, 2018

Im not sure how to tell access to insert it as another record.

ria4life · Dec 7, 2018

ive tried this but got an error

Code:

Private Sub CmdSave_Click()
If Me.CmdSave.Visible = "True" Then

sql = "INSERT INTO TBL_Pick_Selection([Employee_ID],[Date_Selected])" _
    & "VALUES ('" & Me.Text92 & "','" & Me.Sig1 & "')" _
    & "VALUES ('" & Me.Text92 & "','" & Me.Sig2 & "')"

CurrentDb.Execute sql
Else
End If
End Sub

Micron · Dec 7, 2018

don't understand your question/problem. Does it mean that you don't realize that INSERT INTO is a sql statement that appends a record? Or are you thinking that by adding additional fields and values to the sql that it will create multiple records? That isn't true for sure.

EDIT. posted before I saw your last reply
Multiple records will require multiple executions of the sql with the values you need for each record. You need a loop through some sort of recordset. You cannot have multiple VALUE statements in the same INSERT INTO.
Where do you expect the different values to come from?

ria4life · Dec 7, 2018

I'm trying to add multiple records using the same sql statement

plog · Dec 7, 2018

Oh, ok. For every record you want to add you must execute another INSERT INTO statement. My advice would be to create a VBA array (google that), put your values that need to go into the database into that array (google "initialize VBA array"). Then put your INSERT INTO statement inside a for loop (google that) that goes from the begginning of your array until its end. Each time using the next element of the array in your INSERT INTO statement.

Micron · Dec 8, 2018

To each their own, I guess. I'd rather work with a recordset.

plog · Dec 8, 2018

Can you easily get form controls into a recordset?

June7 · Dec 8, 2018

Why are there multiple SigX controls? Screams 'I am not normalized data structure'.

Micron · Dec 8, 2018

plog said:
Can you easily get form controls into a recordset?

If you mean the fields they are bound to, yes. Can open a recordset clone, although it wouldn't be the only way.

plog · Dec 8, 2018

No, I mean controls, as in the poster's issue. Sounds like its an unbound form--otherwise no need for an INSERT statement.

Screams 'I am not normalized data structure'.

I'm the first on this site to lead the yelling at people for not normalizing, but this is not a scream. It's not even a polite whisper. Check the SQL, there are 2 fields in the table, neither named anything alarming and they are trying to put data into the table by adding multiple rows, not numerated field names.

June7 · Dec 8, 2018

ria4life said:
EG: id like to copy values from sig2, sig3 sig4 etc...
[/PHP]

Yes, if this is an UNBOUND form, I could see these as multiple controls feeding data to same field. So how many etc are there? Will they all have input?

Code:

For x = 1 To etc
    If Not IsNull(Me("Sig" & x)) Then
        CurrentDb.Execute "INSERT INTO TBL_Pick_Selection([Employee_ID],[Date_Selected]) " _
            & "VALUES ('" & Me.Text92 & "',#" & Me("Sig" & x) & "#)"
    End If
Next

If Employee_ID is a number type field, remove the apostrophe delimiters. If Date_Selected is a true date/time type field then use # delimiters.

And to make sure there are no duplicate Employee_ID/Date_Selected pairs allowed, set unique compound index in table, in case button is clicked more than once.

Micron · Dec 8, 2018

Maybe it is but I didn't make that assumption. More of "this is what I have, this is what I need" would help, rather than just "how can I make this work? when people post issues. Further to our speculation, perhaps it's a continuous or datasheet form and OP wants to get repeat appends from one control/field in different records along with other fields - hence the repeat of Text92. If you think that's possible, then I don't see how it could be unbound. No doubt we know there can only be one instance of a control name on a form, so why the repeat of Text92? Navigation is at play? There's a lot missing from this in terms of information. In addition, no doubt you've seen many questions on how to duplicate records from one place to another, which was what I originally figured might be going on. Those are the reasons I surmised the form could be bound.

It's time for the OP to weigh in.

Insert Into Help (1 Viewer)

ria4life

Registered User.

plog

Banishment Pending

ria4life

Registered User.

ria4life

Registered User.

Micron

AWF VIP

ria4life

Registered User.

plog

Banishment Pending

Micron

AWF VIP

plog

Banishment Pending

June7

AWF VIP

Micron

AWF VIP

plog

Banishment Pending

June7

AWF VIP

Micron

AWF VIP

Similar threads

Users who are viewing this thread