Form to view newly created records

[aron.ridgway]

I have a piece of code that is creating new records when the form loads using recordsets.

I need a subform to show only the newly created records, any help would be great!

thanks

 

[pr2-eugin]

You can use the @@IDENTITY to get the AutoNumber of the newly created record and filter the SubForm based on that.

 

[aron.ridgway]

There could be between either 1 to say 50 new records that get created. Will this still work on multiple new records?

 

[Dlovan-IT]

you need the loop after finished the loop , update subform by sql it is better to make condition if you want

 

[pr2-eugin]

As the records are created add them as a comma separated string then use that in the filter.

 

[aron.ridgway]

that sounds like it could work, have you got any sample code using that process to get me started?

thanks

 

[pr2-eugin]

Unfortunately, the method of creating records on form open is a little bit unconventional and I don't think it is as easy as possible to get the code, if you could share the code you have to add records on Form open; we could amend the code to adapt to your requirement.

 

[aron.ridgway]

my code is as follows

Private Sub Form_Load()

Dim rstSubForm As Recordset
Dim rs As Recordset
Set rs = CurrentDb.OpenRecordset("tblreceivedetail")
Set rstSubForm = Forms!frmReceive!sfrmReceiveDetail.Form.Recordset

With rs
While Not rstSubForm.EOF

.AddNew
.Fields("OrderDetailFK") = rstSubForm.Fields("OrderDetailPK")
.Fields("UserFK") = rstSubForm.Fields("UserFK")
.Fields("ReceiveFK") = rstSubForm.Fields("ReceivePK")

.Update

   rstSubForm.MoveNext
   
   Wend
   .Close
End With
Me.Requery
End Sub

 

[pr2-eugin]

Try this untested code.

Private Sub Form_Load()
    Dim rstSubForm As DAO.Recordset
    Dim rs As DAO.Recordset, tmpRS As DAO.Recordset
    Dim filStr As String
    
    Set rstSubForm = Forms!frmReceive!sfrmReceiveDetail.Form.Recordset

    While Not rstSubForm.EOF
        CurrentDB.Execute("INSERT INTO tblreceivedetail (OrderDetailFK, UserFK, ReceiveFK) VALUES (" & _
                           rstSubForm.Fields("OrderDetailPK") & ", " & rstSubForm.Fields("UserFK") & ", " & _
                           rstSubForm.Fields("ReceivePK") & ")"
        Set tmpRS = CurrentDB.OpenRecordset("SELECT @@IDENTITY AS LastID;")
        filStr = filStr & tmpRS!LastID & ","
        tmpRS.Close
        Set tmpRS = Nothing
        rstSubForm.MoveNext
    Wend
    
    If Len(filStr) > 0 Then 
        filStr = Left(filStr, Len(filStr) - 1)
        Me.Recordsource = "SELECT * FROM theTableTheFormIsBased WHERE autoIDFieldName IN (" & filStr & ")
    End If
    
    Me.Requery
End Sub

 

[aron.ridgway]

when I copy the code into VBA I the following is red

        CurrentDB.Execute("INSERT INTO tblreceivedetail (OrderDetailFK, UserFK, ReceiveFK) VALUES (" & _
                           rstSubForm.Fields("OrderDetailPK") & ", " & rstSubForm.Fields("UserFK") & ", " & _
                           rstSubForm.Fields("ReceivePK") & ")"

 

[pr2-eugin]

Sorry a Closing paren is missing in the above statement, try this.

CurrentDb.Execute "INSERT INTO tblreceivedetail (OrderDetailFK, UserFK, ReceiveFK) VALUES (" & _
                   rstSubForm.Fields("OrderDetailPK") & ", " & rstSubForm.Fields("UserFK") & ", " & _
                   rstSubForm.Fields("ReceivePK") & ")"

 

[aron.ridgway]

thank you, I have just tried the code and get the following run time;

Run time 2580

The record source 'Select * FROM thetableTheFormIsBased Where autoIDFieldName IN (35,36) specified on this form or report does not exist

any ideas?

 

[aron.ridgway]

the following code gets highlighted when debugged

Me.RecordSource = "SELECT * FROM theTableTheFormIsBased WHERE autoIDFieldName IN (" & filStr & ")"

 

[pr2-eugin]

WoW ! I wrote the name of the table you are dealing with, as FROM. You need to replace theTableTheFormIsBased with the actual name of the table the form is based on.

 

[pr2-eugin]

Okay if it is the subform you want to set the recordsource to then change the Me.Recordsource to Me!sfrmReceiveDetail.Form.Recordsource followed by a requery and refresh.

 

[aron.ridgway]

I have changed it to the following, but it says Access cant find the field 'sfrmReceiveDetailEntry' referred to in your expression?

 Me!sfrmReceiveDetailEntry.Form.RecordSource = "SELECT * FROM qryReceiveDetailEntry WHERE autoIDFieldName IN (" & filStr & ")"
        Me!sfrmReceiveDetailEntry.Form.Requery

 

[aron.ridgway]

I have amended the code to, which no longer errors but the 2nd subform is still blank. I have attached an image the 2nd subform on the right is the one im trying to populate

 If Len(filStr) > 0 Then
        filStr = Left(filStr, Len(filStr) - 1)
        Forms!frmReceive!sfrmReceiveDetailEntry.Form.RecordSource = "SELECT * FROM qryReceiveDetailEntry WHERE autoIDFieldName IN (" & filStr & ")"
        Forms!frmReceive!sfrmReceiveDetailEntry.Form.Requery
    End If

 

[pr2-eugin]

Please don't get offended when I say this, but for crying out loud.

I have no clue what your field names are, so I used random names like theTableTheFormIsBased and autoIDFieldName it is your responsibility to change them according to your design. Is there a column in your table called autoIDFieldName, if the answer is No, then change it.

What is the SQL Query of qryReceiveDetailEntry? Make sure your Query includes the column which you are trying to filter.

 

[aron.ridgway]

Haha sorry my bad i totally missed that! i have replaced with the ID number of my query.

Im still getting a blank subform though? it is creating the new records fine just not displaying them?

 

[pr2-eugin]

Upload a Stipped DB.

How to Upload a Stripped DB.

To create a Sample DB (to be uploaded for other users to examine); please follow the steps..

1. Create a backup of the file, before you proceed..
2. Delete all Forms/Queries/Reports that are not in Question (except the ones that are inter-related)
3. Delete auxiliary tables (that are hanging loose with no relationships).
4. If your table has 100,000 records, delete 99,990 records.
5. Replace the sensitive information like Telephone numbers/email with simple UPDATE queries.
6. Perform a 'Compact & Repair' it would have brought the Size down to measly KBs..
7. (If your Post count is less than 10 ZIP the file and) Upload it..

Finally, please include instructions of which Form/Query/Code we need to look at. The preferred Access version would be A2003-A2007 (.mdb files)