Syntax q: APPEND to tables from unbound form (1 Viewer)

[NBRJ]

I have a unbound form (called fBulkRequest) on which I'd like users to makes some selections and then click a button which then adds records to a table called tRequestDetail. I'm having trouble figuring out the syntax to do this:

• lstRequest (listbox), bound column (1) is IDRequest. I want to pass the selected item from lstRequest (only 1 selection can be made) into tRequestDetails.FIDRequest.
• cboSoftwareItem (combobox), bound column (1) is IDVersion. I want to pass the selected item from cboSoftwareItem into tRequestDetails.FIDVersion
• txtConfiguration (txtbox unbound). I want to create a new record in tConfigInfo (IDConfigInfo, Configuration) and pass THAT IDConfigInfo into tRequestDetails.FIDConfigInfo.

BUT there is also a listbox called lstLocation that displays a list of records. For each item in that currently displayed list I want to append the above values and then put each item's IDLocation (the bound column of lstLocation) in tRequestDetails.FIDLocation.

At some point, I need to put error checking into this, but I'll like to figure that out after this. I've had a read of a few things and have assumed that the INSERT INTO SQL function is what's needed, but I'm confused about how to pass the various values in AND 1) for each item in the lstLocation cycle though and add a new record as well as 2) create one new record in another table and pull that value back.... This is my first attempt on notepad, but it's a bit late and I'm dog tired for me to risk playing with the form right now, but comments as to if I'm in the right direction or barking up the wrong tree would be greatly appreciated:

Private Sub BulkAdd_Click()

Dim NewCI as String
Dim intX As Integer
Dim strADDSTUFF as String

NewCI = "INSERT INTO tConfigInfo (Configuration) SELECT txtConfiguration FROM fBulkRequest;"
[COLOR="SeaGreen"]How to return the new IDCOnfigInfo to then use below?[/COLOR]

For intX = 0 To Me.lstLocation.ListCount - 1
  
Me.lstLocation.Selected(intX) = True

strADDSTUFF = "INSERT INTO tRequestDetail (FIDRequest, FIDVersion, FIDLocation, FIDConfigInfo)"
& " SELECT " & Me.lstRequest.Selected.Column(0) & ", "
& " SELECT " & Me.cboSoftwareItem.Column(0) & ", "
& " SELECT " & Me.lstLocation.Column(0) ", " 
& " SELECT ???????;" [COLOR="Seagreen"]'how to return the IDConfigInfo from above[/COLOR]

Next intX 

End Sub

 

[MarkK]

There are essentially two ways to get the new primary key back from doing an insert. 1) use a recordset, 2) use an insert query and '@@Identity' on the database that did the insert. I'll show code for both.

1) Recordset

dim rst as dao.recordset
dim [COLOR="DarkRed"]newID[/COLOR] as long

set rst = currentdb.openrecordset("tConfigInfo")
with rst
   .addnew
   !Configuration = Me.txtConfiguration
   [COLOR="DarkRed"]newID[/COLOR] = !FIDConfigInfo
   .update
   .close
end with

2) Insert Query

const SQL_INSERT as string = _
   "INSERT INTO tConfigInfo ( Configuration ) " & _
   "VALUES ( prm0 )"

dim [COLOR="DarkRed"]newID[/COLOR] as long
   
with currentdb
[COLOR="Green"]   'insert data using parameterized querydef[/COLOR]
   with .createquerydef("", SQL_INSERT)
      .parameters("prm0") = Me.txtConfiguration
      .execute dbFailOnError
      .close
   end with
[COLOR="Green"]   'retrieve @@Identity value from the database object, in this case CurrentDb[/COLOR]
   with .openrecordset("SELECT @@Identity")
      [COLOR="DarkRed"]newID[/COLOR] = .Fields(0)
      .close
   end with
end with

The second approach is more verbose, but probably faster, if that matters, since in the second case no preparation is ever made to retrieve any data. Whereas in 1), we are essentially preparing to get ALL the records from the table (which we never do, because we run .AddNew right away).

In terms of a listbox with multiple selections, check out the ItemsSelected property, which returns a collection of only the values in the selected rows. Then you wont have to do this check . . .

Me.lstLocation.Selected(intX) = True

. . . in every iteration of the the loop.

SQL for your final insert should probably look something like this . . .

private const SQL_INSERT = _
   "INSERT INTO tRequestDetail " & _
      "( FIDRequest, FIDVersion, FIDLocation, FIDConfigInfo ) " & _
   "VALUES " & _
      "( p0, p1, p2, p3 )"

. . . and then you either use a temp querydef (as in 2, above) and set those parameters (and run .execute), or you have to explicitly string together your delimited values in the last line of the SQL.

Hope this helps,

 

[NBRJ]

I'm sorry for the tardiness of my response. I've been away over the Easter period and just got back into this. I now regret the break, I've forgotten everything I'm very grateful for your detailed response. However, I am slightly confused. I've been trying to implement this, but I can't, as I'm not quite sure what I'm doing as this code is beyond what I've used so far. The main sticking point, for me logically, is cycling through the items in the second listbox.

To clarify that my original post, the list entries are NOT selected in lstLocation (there could be 100s), they're just there (they've been filtered via 3 combo boxes to get a restricted list).

So I have the following form fields:

• lstRequest (list - user must select ONE entry here). Bound field is IDRequest.
• lstLocation (list - filtered via 3 combo boxes, no selection needed, as many in list). Bound field is IDLocation.
• cboSoftwareItem (combobox - user makes a selection). Bound field is IDVersion.
• txtConfiguration (long text field)

Equal to the number of items in lstLocation (remember, these are not selected, so not sure the SelectedItems property will work?) I need to create the same number of records in tRequestDetail that has the following info:

• IDRequestDetails (PK) - autonumber
• FIDRequest (FK) - IDRequest as selected from lstRequest
• FIDLocation (FK) - IDLocation of each record will hold an ID of an entry in lstLocation.
• FIDVersion (FK) - IDVersion from cboConfigInfo
• FIDConfigInfo (FK) - IDConfigInfo retrieved as perviously detailed in MarkK's post.

I assumed from a few posts I somehow have to cycle through the list of lstLocation, and have it select an entry, run an INSERT INTO tRequestDetail the above data, move to next item in lstLocation and repeat until end.

I'm just having trouble understanding how the code goes to get all this into one subroutine (including MarkK's earlier suggestion how to retrieve the tConfigInfo.IDConfigInfo of the newly created record). A attempt below with questions/comments in green:

[B]Private Sub cmdBulkUpdate_Click()[/B]

[COLOR=SeaGreen]'---------FUNCTION--------
'adds a record to tConfigInfo and returns it's ID.[/COLOR]

Const NewCIItem As String = _
   "INSERT INTO tConfigInfo ( Configuration ) " & _
   "VALUES ( prm0 )"[COLOR=SeaGreen]
'why const and not dim?[/COLOR]

Dim newID As Long
   
With CurrentDb
[COLOR=Purple]  [I] 'insert data using parameterized querydef[/I][/COLOR]
   With .CreateQueryDef("", NewCIItem) [COLOR=SeaGreen]'  ""?[/COLOR]
      .Parameters("prm0") = Me.txtConfiguration
      .Execute dbFailOnError
      .Close
   End With
[COLOR=Purple] [I]  'retrieve @@Identity value from the database object, in this case CurrentDb[/I][/COLOR]
   With .OpenRecordset("SELECT @@Identity")
      newID = .Fields(0)
      .Close
   End With
End With

[COLOR=SeaGreen]'---------FUNCTION--------
'equal to the number of locations in lstLocation, add a new record in tRequestDetail
'(including the returned ID above from tConfigInfo)[/COLOR]
Const strBULKUPDATE As String = _
    "INSERT INTO tRequestDetail " & _
    "(FIDRequest, FIDVersion, FIDLocation, FIDConfigInfo) "

Dim intX As Integer

For intX = 0 To Me.lstLocation.ListCount - 1[COLOR=SeaGreen] 'for the number of items in the list
[COLOR=Black]Me.lstLocation.Selected(intX) = True[/COLOR][B] 'select the item? [/B]Is this what this is doing? 
Or is it asking if it's selected? I need to do the former.[/COLOR]

strBULKUPDATE = strBULKUPDATE & "SELECT " & _[COLOR=SeaGreen] 'or VALUES?[/COLOR]
Me.lstRequest.Selected.Column(0) & ", " & _
Me.cboSoftwareItem.Column(0) & ", " & _
Me.lstLocation.Column(0) & ", " & _
newID & " );"
 
Next intX

[B]End Sub[/B]

I will be adding in more to this, there's more data from the form that goes in a different table, a little like the tConfigInfo stuff, but I hope to work out that bit, once this is working. If this beast of a form eventually works I will be one happy person... until the next problem

Sorry for the questions, full of dumb this week.

 

[NBRJ]

Ok, just run the code above. Used the debrug.print to check that new record's PK value is returned (and it does, what else?! ). Of course, my part of it fails to do anything (of course it does). I'm thinking it's something to do with the cycling through the lstLocation without them being selected (but I don't want to select them). Please help me

[COLOR=SeaGreen]'---------FUNCTION--------
'equal to the number of locations in lstLocation, add a new record in tRequestDetail
'(including the returned ID above from tConfigInfo)[/COLOR]
Dim strBULKUPDATE As String[B][COLOR=SeaGreen] 'changed from const as errored: assignment to constant not permitted[/COLOR][/B][COLOR=SeaGreen].
I assume because this statement is fed in values so not constant.[/COLOR]

strBULKUPDATE = "INSERT INTO tRequestDetail (FIDRequest, FIDVersion, FIDLocation, FIDConfigInfo) "

Dim intX As Integer

For intX = 0 To Me.lstLocation.ListCount - 1[COLOR=SeaGreen] 'for the number of items in the list[/COLOR]
Me.lstLocation.Selected(intX) = True[COLOR=SeaGreen] 'select the item? Is this what this is doing?
'Or is it asking if it's selected? I need to do the former.[/COLOR]

strBULKUPDATE = strBULKUPDATE & "SELECT " & _
Me.lstRequest.Column(0) & ", " & _[COLOR=SeaGreen] ' errored with: Me.lstRequest.Selected.Column(0) & ", " & _  
'(this is the selected item lst)[/COLOR]
Me.cboSoftwareItem.Column(0) & ", " & _ 
Me.lstLocation.Column(0) & ", " & _[COLOR=SeaGreen] '(this is the non selected items lst,
'that we need to cycle through)[/COLOR]
newID & " );"
 
Next intX

 

[NBRJ]

Ok, I'm using the debug.print which I thought would be helpful to work out the text to get the IDs of the fields I mentioned, then I can work out how to add them into the INSERT INTO loop:

Dim intX As Integer
For intX = 0 To Me.lstLocation.ListCount - 1

Debug.Print "tRequest ???:"; [B]Me.lstRequest.ItemsSelected(0)
[/B]Debug.Print "tVersion  PK ID:"; Me.cboSoftwareItem.Value
Debug.Print "tLocation PK ID:"; Me.lstLocation.Column(0, intX)
Debug.Print "tConfig PK ID:"; returnedConfigInfoID
Debug.Print "NEXT"

Next intX

This is returning the correct IDs, and cycling through the number of items in lstLocation, except for the lstRequest:

tRequest ???: 1
tVersion PK ID:7
tLocation:45
tConfig PK ID: 35
NEXT

The number for lstRequest is the position in the list, not the bound column value (IDRequest). How do I return that, or even the contents of one of the other columns in the list?

I then assumed that the others will work like this (replacing the Debug.Print text in the first code section above):

Dim insertRequestDetail As String
insertRequestDetail = "INSERT INTO tRequestDetail (FIDRequest, FIDVersion, FIDLocation, FIDConfigInfo) VALUES ('" & _
                    lstRequest.ItemSelected.Value & "','" & _ [COLOR="SeaGreen"]'Not working. Compile error: 
Method or data member not found. Well, since I don't know how to select the ID value anyway, 
I didn't expect it to...[/COLOR]
                    Me.cboSoftwareItem.Value & "','" & _
                    Me.lstLocation.Column(0, intX) & "','" & _
                    returnedConfigInfoID & "');"
 DoCmd.RunSQL insertstring

Can anybody say if this is completely the wrong direction? Thank you.

 

[MarkK]

If the selected status of the listbox items is not relevant, why use a listbox at all? Instead of supplying a RowSource to the list, use that RowSource to open a recordset directly, and use that recordset to control your loop. How is that listbox populated with data?

 

[NBRJ]

Well, I've figured that one out (and the unselected list is a visual confirmation that the news records are created for the correct locations).

It's now the selected list that's not working for me (see last two posts).

 

[NBRJ]

Code so far:

[B]Private Sub cmdBulkUpdate_Click()[/B]

[COLOR=SeaGreen]'---------FUNCTION--------
'adds a record to tConfigInfo and returns it's ID.[/COLOR]

Const NewCIItem As String = _
   "INSERT INTO tConfigInfo ( Configuration ) " & _
   "VALUES ( txtCIfromBIForm )"

Dim newID As Long
   
With CurrentDb
[I][COLOR=SeaGreen]   'insert data using parameterized querydef[/COLOR][/I]
   With .CreateQueryDef("", NewCIItem) '  ""?
      .Parameters("txtCIfromBIForm") = Me.txtConfiguration
      .Execute dbFailOnError
      .Close
   End With
[COLOR=SeaGreen][I]   'retrieve @@Identity value from the database object, in this case CurrentDb[/I][/COLOR]
   With .OpenRecordset("SELECT @@Identity")
      returnedConfigInfoID = .Fields(0)
      .Close
   End With
End With

[COLOR=SeaGreen]'---------FUNCTION--------
'equal to the number of locations in lstLocation, add a new record in tRequestDetail
'(including the returned ID above from tConfigInfo)[/COLOR]

Dim intX As Integer
For intX = 0 To Me.lstLocation.ListCount - 1

[COLOR=SeaGreen]'Debug.Print "tRequest ???:"; Me.lstRequest.Column(0)
'Debug.Print "tVersion  PK ID:"; Me.cboSoftwareItem.Value
'Debug.Print "tLocation PK ID:"; Me.lstLocation.Column(0, intX)
'Debug.Print "tConfig PK ID:"; returnedConfigInfoID
'Debug.Print "NEXT"[/COLOR]

insertRequestDetail = "INSERT INTO tRequestDetail (FIDRequest, FIDVersion, FIDLocation, FIDConfigInfo) VALUES ('" & _
                    lstRequest.Column(0) & "','" & _
                    Me.cboSoftwareItem.Value & "','" & _
                    Me.lstLocation.Column(0, intX) & "','" & _
                    returnedConfigInfoID & "');"
DoCmd.RunSQL insertRequestDetail

Next intX
[B]
End Sub[/B]

The trouble I have with this, although it's working (great) it's asking me "You are about to append 1 row(s)" message for each row in lstLocation. Is there anyway to get it to cycle through the whole lot without this?

 

[NBRJ]

Ok, ignore me, done this:

[COLOR=SeaGreen]'---------FUNCTION--------
'equal to the number of locations in lstLocation, add a new record in tRequestDetail
'(including the returned ID above from tConfigInfo)[/COLOR]

[B]If MsgBox("Are you sure you want to append " & Me.[lstLocation].ListCount & " records.", vbYesNo + vbQuestion, "Appending Records") = vbNo Then Exit Sub[/B]

Dim intX As Integer
For intX = 0 To Me.lstLocation.ListCount - 1

insertRequestDetail = "INSERT INTO tRequestDetail (FIDRequest, FIDVersion, FIDLocation, FIDConfigInfo) VALUES ('" & _
                    lstRequest.Column(0) & "','" & _
                    Me.cboSoftwareItem.Value & "','" & _
                    Me.lstLocation.Column(0, intX) & "','" & _
                    returnedConfigInfoID & "');"
DoCmd.RunSQL insertRequestDetail
[B]DoCmd.SetWarnings False[/B]
Next intX

All seems to be working, thanks to MarkK for his original post regaurding the @@Identity function.

 

[Minty]

Instead of docmd.runsql use CurrentDb.Execute insertrequestdetail , dbFailOnError

 

[NBRJ]

This monster form is working, and now with error checking and everything also added in (and still working!).

It's somewhat annoying that it looks like a fairly small form, which doesn't at all illustrate the effort involved getting it working and how much it's doing. I've learnt a LOT from just one form.

My thanks goes to MarkK and all the others so far who have helped me on this and the other threads. That you give your time to help people like me who are just starting out is very generous and, certainly from me, much appreciated.

 

[MarkK]

Congrats to you. Excellent stick-with-it-ness on your part!