Insert several records at once

[mxp346]

I have a form with the following text fields... txtID1,txtDesc1, txtID2, txtDesc2, txtID3, and txtDesc3. The user fills out all of these fields and then there is a button to add the records to the table, tblOccurance. Each of the sets of ID's and Desc's stay together as 1 entry into the table. I have experience with Access but not SQL. Can someone give me a general idea of what I would have to write for the SQL. Thank you.

 

[scking]

I believe you merely need to add a single record if I understand what you are trying to say. A record is composed of a number of fields that are associated to a single unique key. Since you are talking about fields on a single form, and I am assuming this is a single record (with no accompanying subforms or related data, then the fields would be elements of a record.

I'm on the road so I will TRY to remember the format exactly from memory but you could always use help for the format. Please either verify or someone correct my syntax.

strSQL = "INSERT (fld1, fld2, fld3) INTO YourTablename VALUES('" & Me.txtVal1 & "', Me.numVal2, Me.numVal3)"

Steve King

Growth follows a healthy professional curiosity

 

[mxp346]

There are three records. The first contains txtID1 and txtDesc1. The second contains txtID2 and txtDesc2. The third contains txtID3 and txtDesc3. The form allows for several records to be entered into the form at the same time so the user can update the table with a few records at a time instead of one at a time.

 

[mxp346]

The form looks like this...

*****************************************
*   txtID1   txtDesc1    txtOccNum1     *
*                                       *
*   txtID2   txtDesc2    txtOccNum2     *
*                                       *
*   txtID3   txtDesc3    txtOccNum3     *
*                                       *
*    -----------------                  *
*   |AddRecords Button|                 *
*    -----------------                  *
*****************************************

I can't figure out the VB code for the AddRecords button. I want the code to take the values in txtID1, txtDesc1, and txtOccNum1 and store them as the next row in the table tblOccurance(ID, Desc, Occ). Then take txtID2, txtDesc2, txtOccNum2 and store them in the next row.

 

[braindead2]

loop
for x - 1 to 3
strSQL = "INSERT (ID, Desc, Occ) INTO tblOccurance VALUES(Me("txtID" & x) , Me("txtDesc" & x, Me("txtoccNum" & x)"
next x

 

[mxp346]

I'm getting an error... Compile Error: Expected: end of Statement and it highlights txtID. Any suggestions?

 

[scking]

Look closely at the SQL you have created. What is inconsistent? Paste the SQL into a SQL window of the query wizard then switch to view/design. Does it display correctly or prompt you with an error number? Reduce the SQL to a single value and try the same thing. Build an append query using other tables and view the SQL window for the correct format. Check out the help files for the keyword 'INSERT' and/or SQL. If you used the SQL assignment statement submitted by braindead2 I can see a missing parenthesis at least. If you use the syntax Me. intellisense will prompt you with a list of the available controls if your syntax is not already incorrect. Using debug mark the assignment line with a breakpoint and inspect the variable values. Input the exact line of code into the immediate window and keep modifying it until it works. Experiment and debug. The answer is available to those who use standard troubleshooting or debugging techniques.

Steve King Growth follows a healthy professional curiosity

 

[mxp346]

I've been playing around with this for a while and I have come up with the following...

Private Sub cmdAddRecord_Click()

Dim SQL As String
Dim ID As Control
Dim Desc As Control
Dim Cat As Control
Dim Occ As Control

Set ID = Forms!FrmOccurance!txtID1
Set Desc = Forms!FrmOccurance!txtDesc1
Set Cat = Forms!FrmOccurance!txtCat1
Set Occ = Forms!FrmOccurance!txtOcc1

strSQL = "INSERT INTO tblOccurance(OccID, OccDesc, OccCat, OccNumber) VALUES(ID, Desc, Cat, Occ)"

End Sub

The strSQL line is all on 1 line. The code isn't producing any errors but it is also not doing anything. Any suggestions on what part needs to be fixed?

 

[scking]

Private Sub cmdAddRecord_Click()

Dim strSQL As String
Dim strID As String
Dim strDesc As String
Dim strCat As String
Dim strOcc As String

strID = Forms!FrmOccurance!txtID1
strDesc = Forms!FrmOccurance!txtDesc1
Set strCat = Forms!FrmOccurance!txtCat1
Set strOcc = Forms!FrmOccurance!txtOcc1

strSQL = "INSERT INTO tblOccurance(occID, OccDesc, OccCat, OccNumber) VALUES('" & strID & "', '" & strDesc & "', '" & strCat & "', '" & strOcc & "');"

End Sub

Steve King Growth follows a healthy professional curiosity

 

[scking]

Forgot to change the last two 'Set' statements. the SQL statement uses text (string) data NOT controls. You need to delimit the data correctly to put it in a SQL statement. Use ' for text type data, # for date type data, and numberics don't require delimiting. The delimiter tells access that, even though you are providing it with text that it goes into a table field that is typed as you delimiter.

strCat = Forms!FrmOccurance!txtCat1
strOcc = Forms!FrmOccurance!txtOcc1

Steve Growth follows a healthy professional curiosity