Enter Data to Subform with some data complete 1

[PotatoDave]

I am trying to add a form where a data sheet is used to add rows to a table. I want the full data in the table to be made up a combination of the combo boxes on the main form, and then also from the datasheet. The situation is for entering orders into the datasheet, the depot, date, order type and time (wave - AM or PM) will be the same for each item (and should come from the combo boxes), but i want a new record for each product with quantity and the same data in the first few columns. The catch is I want the datasheet to have 2 columns, one with product code and the other for qty, and i want the datasheet to have a full list of active product codes (from a query), so that they just enter the qty required. I have tried the following to update the subform:

Private Sub Form_AfterInsert()
Dim prodq As DAO.Recordset
Dim db As Database
Dim LSQL As String

Set db = CurrentDb()

Set prodq = db.OpenRecordset("ActiveProd", dbOpenDynaset)

If prodq.RecordCount > 0 Then
prodq.MoveFirst
Do Until prodq.EOF
LSQL = "insert into data (commodity, Date, Wave, OType, Depot)"
LSQL = LSQL & " values ("
LSQL = LSQL & "'" & prodq.Fields("Code").Value & "', " & Date & "', " & Wave & "', " & OType & "', " & Depot & ")"

'Perform SQL
db.Execute LSQL

prodq.MoveNext
End If
Data_subform.Requery
End Sub

This is a bit of a mess from trying stuff from several different websites, I get no error, but the subform doesn't update and no data is added to the data table.

 

[VBslammer]

There must be a typo since I don't see the bottom of the Do Loop, and the SQL should raise an error if executed as is. Are you sure this procedure is firing at all? It's slightly unusual to perform inserts in the Form_AfterInsert() event.

Anyway, when I'm debugging dynamic SQL I always Debug.Print the SQL string to the immediate window so I can tell if it's concatenating right. You should set a break point at the beginning of the loop so you can walk through each iteration and check the looping progress:

Private Sub Form_AfterInsert()
  Dim prodq As DAO.Recordset
  Dim db    As DAO.Database
  Dim LSQL  As String

  Set db = CurrentDb()

  Set prodq = db.OpenRecordset("ActiveProd", dbOpenDynaset)

  If prodq.RecordCount > 0 Then   [green]'set a breakpoint here, press F8 to step into[/green]
    Do Until prodq.EOF
      LSQL = "insert into data (commodity, Date, Wave, OType, Depot)"
      LSQL = LSQL & "  values ('" & prodq("Code") & "',#"
      LSQL = LSQL & Date & "#,'" & Wave & "','" & OType & "'," & Depot & ")"
        
      Debug.Print LSQL    [green]'examine the string[/green]
    
      db.Execute LSQL        
      prodq.MoveNext    
    Loop
  End If

  Data_subform.Requery

End Sub

VBSlammer

"You just have to know which screws to turn." - Professor Bob

 

[PotatoDave]

Thanks

Using the tips you gave i found that the following code works:
Private Sub Form_AfterInsert()
Dim prodq As DAO.Recordset
Dim db As Database
Dim LSQL As String

Set db = CurrentDb()

Set prodq = db.OpenRecordset("ActiveProd", dbOpenDynaset)
If prodq.RecordCount > 0 Then
prodq.MoveFirst
Do Until prodq.EOF
LSQL = "insert into data (commodity, ODate, Wave, OType, Depot,concat)"
LSQL = LSQL & " values ("
LSQL = LSQL & "'" & prodq.Fields("Code").Value & "', #" & ODate & "#, '" & Wave & "', '" & OType & "', '" & Depot & "', '" & concat & "');"
Debug.Print LSQL
'Perform SQL
db.Execute LSQL

prodq.MoveNext
Loop
End If
Data_subform.Requery
End Sub


I have added a concat field that links the Date, Wave and Depot and used this as the link field (Access was only recognising the first 3 fields and I need 4) and now the form works

BUT...
I now get a blank row at the top of the data sheet, and then my product list beneath it. Any ideas how I can prevent this happening / remove it automatically - not a major issue but just an annoyance. Thanks again for your help.

 

[VBslammer]

You must have an empty record in your "ActiveProd" table that needs to be deleted.

VBSlammer

"You just have to know which screws to turn." - Professor Bob