Go Back   Access World Forums > Microsoft Access Discussion > Modules & VBA

 
Reply
 
Thread Tools Rate Thread Display Modes
Old 12-07-2018, 10:46 AM   #1
ria4life
Newly Registered User
 
Join Date: Feb 2016
Posts: 39
Thanks: 13
Thanked 0 Times in 0 Posts
ria4life is on a distinguished road
Insert Into Help

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 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 "')"

CurrentDb.Execute sql
Else
End If
End Sub 

ria4life is offline   Reply With Quote
Old 12-07-2018, 10:50 AM   #2
plog
AWF VIP
 
Join Date: May 2011
Posts: 8,997
Thanks: 10
Thanked 2,160 Times in 2,115 Posts
plog is a jewel in the rough plog is a jewel in the rough plog is a jewel in the rough
Re: Insert Into Help

So what have you tried? Getting any errors? I mean, you just follow the pattern you have going.
plog is online now   Reply With Quote
Old 12-07-2018, 10:55 AM   #3
ria4life
Newly Registered User
 
Join Date: Feb 2016
Posts: 39
Thanks: 13
Thanked 0 Times in 0 Posts
ria4life is on a distinguished road
Re: Insert Into Help

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

ria4life is offline   Reply With Quote
Old 12-07-2018, 11:03 AM   #4
ria4life
Newly Registered User
 
Join Date: Feb 2016
Posts: 39
Thanks: 13
Thanked 0 Times in 0 Posts
ria4life is on a distinguished road
Re: Insert Into Help

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
ria4life is offline   Reply With Quote
Old 12-07-2018, 11:07 AM   #5
Micron
Newly Registered User
 
Join Date: Oct 2018
Location: Ontario, Canada
Posts: 66
Thanks: 0
Thanked 10 Times in 9 Posts
Micron is on a distinguished road
Re: Insert Into Help

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?

Last edited by Micron; 12-07-2018 at 11:12 AM.
Micron is offline   Reply With Quote
Old 12-07-2018, 11:08 AM   #6
ria4life
Newly Registered User
 
Join Date: Feb 2016
Posts: 39
Thanks: 13
Thanked 0 Times in 0 Posts
ria4life is on a distinguished road
Re: Insert Into Help

I'm trying to add multiple records using the same sql statement
ria4life is offline   Reply With Quote
Old 12-07-2018, 11:13 AM   #7
plog
AWF VIP
 
Join Date: May 2011
Posts: 8,997
Thanks: 10
Thanked 2,160 Times in 2,115 Posts
plog is a jewel in the rough plog is a jewel in the rough plog is a jewel in the rough
Re: Insert Into Help

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.

plog is online now   Reply With Quote
Old 12-07-2018, 05:27 PM   #8
Micron
Newly Registered User
 
Join Date: Oct 2018
Location: Ontario, Canada
Posts: 66
Thanks: 0
Thanked 10 Times in 9 Posts
Micron is on a distinguished road
Re: Insert Into Help

To each their own, I guess. I'd rather work with a recordset.
Micron is offline   Reply With Quote
Old 12-07-2018, 07:30 PM   #9
plog
AWF VIP
 
Join Date: May 2011
Posts: 8,997
Thanks: 10
Thanked 2,160 Times in 2,115 Posts
plog is a jewel in the rough plog is a jewel in the rough plog is a jewel in the rough
Re: Insert Into Help

Can you easily get form controls into a recordset?
plog is online now   Reply With Quote
Old 12-07-2018, 07:42 PM   #10
June7
Newly Registered User
 
Join Date: Mar 2014
Posts: 960
Thanks: 0
Thanked 217 Times in 217 Posts
June7 will become famous soon enough
Re: Insert Into Help

Why are there multiple SigX controls? Screams 'I am not normalized data structure'.
__________________
To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression. Attachment Manager is below Advanced editor window, click Go Advanced below Quick Reply window.

Last edited by June7; 12-07-2018 at 09:12 PM.
June7 is offline   Reply With Quote
Old 12-07-2018, 08:20 PM   #11
Micron
Newly Registered User
 
Join Date: Oct 2018
Location: Ontario, Canada
Posts: 66
Thanks: 0
Thanked 10 Times in 9 Posts
Micron is on a distinguished road
Re: Insert Into Help

Quote:
Originally Posted by plog View Post
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.
Micron is offline   Reply With Quote
Old 12-07-2018, 09:56 PM   #12
plog
AWF VIP
 
Join Date: May 2011
Posts: 8,997
Thanks: 10
Thanked 2,160 Times in 2,115 Posts
plog is a jewel in the rough plog is a jewel in the rough plog is a jewel in the rough
Re: Insert Into Help

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

Quote:
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.

Last edited by plog; 12-07-2018 at 10:03 PM.
plog is online now   Reply With Quote
Old 12-07-2018, 11:42 PM   #13
June7
Newly Registered User
 
Join Date: Mar 2014
Posts: 960
Thanks: 0
Thanked 217 Times in 217 Posts
June7 will become famous soon enough
Re: Insert Into Help

Quote:
Originally Posted by ria4life View Post
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.
__________________
To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression. Attachment Manager is below Advanced editor window, click Go Advanced below Quick Reply window.

Last edited by June7; 12-08-2018 at 12:03 AM.
June7 is offline   Reply With Quote
Old 12-08-2018, 08:56 AM   #14
Micron
Newly Registered User
 
Join Date: Oct 2018
Location: Ontario, Canada
Posts: 66
Thanks: 0
Thanked 10 Times in 9 Posts
Micron is on a distinguished road
Re: Insert Into Help

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.

Micron is offline   Reply With Quote
Reply

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Problem with Insert Statement strSQL = "INSERT INTO Battlescar Modules & VBA 4 12-08-2017 10:03 AM
INSERT query does not insert all data in query marlan Queries 32 09-17-2015 01:35 PM
Bulk INSERT doesn't insert all records dirkps Modules & VBA 5 03-18-2010 12:20 AM
insert an array with INSERT INTO filo65 Modules & VBA 4 01-04-2006 12:09 AM
[SOLVED] Insert Object without using the Insert Object Open Dialog Box. clivepot Modules & VBA 1 04-17-2003 06:57 AM




All times are GMT -8. The time now is 06:22 AM.


Microsoft Access Help
General
Tables
Queries
Forms
Reports
Macros
Modules & VBA
Theory & Practice
Access FAQs
Code Repository
Sample Databases
Video Tutorials

Featured Forum post


Sponsored Links


Powered by vBulletin®
Copyright ©2000 - 2018, Jelsoft Enterprises Ltd.
(c) copyright 2017 Access World