Add record in VBA with Primary Key autonumber

[Kayleigh]

Working on an add record function which moves records from list available and creates new record in related table with item and logID. The trouble is that the primary key of related table is an autonumber so not sure how to add field in DAO. Have looked at some sample code but couldn't find clear-cut answer...
Code so far:

Private Sub cmdUpdateStudents_Click()

    Dim X As Integer
    Dim i As Variant
    Dim DBS As DAO.Database
    Dim rst As DAO.Recordset
    Dim td As DAO.TableDef
        
   Set DBS = CurrentDb
 
    Set td = DBS.TableDefs!tblIncidentLogDetails
    Set rst = td.OpenRecordset
    
  
 
 


    For Each i In Me.lstPossStudents.ItemsSelected
        rst.AddNew
        rst!fldStudentID = Me.lstPossStudents.ItemData(i)
        rst!fldIncidentLogID = Me.fldIncidentLogID
        Debug.Print Me.lstPossStudents.ItemData(i)
        On Error Resume Next
        rst.Update
    
    Next i
    rst.Close
    Set rst = Nothing
    Set td = Nothing
    Me.Refresh
    
    
    Exit Sub



End Sub

Table structure:
tblIncidentLog (fldIncidentLogID as PK) > tblIncidentLogDetails (fldIncidentLogDetailsID as PK; also contains fldIncidentLogID and fldStudentID)

 

[The_Doc_Man]

When an autonumber PK field exists in a table, the DAO to add a new record omits that field even though it fills in everything else that should be filled in. When you declared an autonumber, in essence you handed over control of that field's contents to Access itself. (Well, to the JET or ACE engines, depending on which version of Access you are running...)

Since it is a PK but also an autonumber, that means you never reference it in any SET sub-clause of an UPDATE query or the INSERT INTO's field-list and VALUE list. You CAN reference it in WHERE clauses for a DELETE or UPDATE. In a DAO/VBA sequence, the PK can never be on the left-hand side of any field assignment statement.

ADDENDUM: You didn't find a clear-cut answer because the field can't be referenced by anything that would assign a value.

 

[Kayleigh]

Thanks for clarifying. So I investigated further and found that the trouble with code was that it does not seem to be assigning ID to unbound textbox (this is unbound form) so it could not use ID for adding records.
My workaround was to use the LogID from previous form (should have assigned on open but did not). Don't think its ideal but it does the job.

 

[Isaac]

It's very hard to tell from your post what you are trying to do, but I think you're confused about the purpose of keys, primary & foreign.

You've got the right idea, in the sense that, "I need to store the parent table's primary key value somewhere in the child table".
Yes, you do - but not in the child table's PRIMARY key! The child table's primary key should remain an autonumber and never be assigned by you.

You store the parent table's primary key value in a Number (long) column in the child table, and that column is now considered a Foreign Key.

 

[Kayleigh]

I believe I do understand that.
After investigating my forms, I found that the parent form was actually unbound so it did not have an ID to send to the child form. So not having this issue any longer!
Though I'm still not sure the best way to send ID from parent form to child (pop-out) form so it can be referenced in DAO recordset?

 

[The_Doc_Man]

If the parent ID is unbound, I don't know if you can build the automatic link that matches parent to child in the sub-form wizard. If you could, Access would automatically supply it. However, if that doesn't work, then... if the ID exists in the parent form and the child form is in a legit sub-form control, then from the sub-form, you might consider referencing the sub-form's parent. See link for details...

SubForm.Parent property (Access)

Office VBA reference topic

docs.microsoft.com

If the ID control in the parent is called PrntID, then from code in the sub-form, you could use Me.Parent.PrntID to get the value of it. NOTE, however, that if the ID in question is bound and the FIELD in the bound table is PrntID, you should name the control something else to avoid confusion. And in that case, use the parent's CONTROL's name rather than the parent's FIELD's name.

 

[Kayleigh]

Would this work for a pop-out form?

 

[The_Doc_Man]

I'm actually a little hazy on that question but I think the answer is NO because a pop-up form doesn't have a parent. It isn't a sub-form, but rather is a new INDEPENDENT form window. I want to stay out of the weeds too deep here, but basically the analogy is that if you launch something using any variant of DoCmd.OpenForm then that form you just opened is NOT a child of the thing that opened it. If you have something that is a form residing in a sub-form control, that IS a child of the form having that control AND you don't launch it with DoCmd.OpenForm. It launches because it is a declared child of the form in which its containing control exists.

Did I answer your question or did I miss your intent?

 

[Kayleigh]

Yes I get that. In my context I am using a pop-out form (unbound) which should be displaying records based on ID of previous form. This usually works although if the first form has opened on a new record it does not seem to have a primary key yet??
This is where I start having issues...

 

[The_Doc_Man]

One possibility is if the thing launching the pop-up CAN cooperate, look into OpenArgs as a means of ID passage. Have the launching form supply the ID in question.

Form.OpenArgs property (Access)

Office VBA reference topic

docs.microsoft.com

If the launching form CANNOT cooperate, then it gets more complex.

You have a design issue if this pop-up NEEDS an ID but one hasn't been assigned yet. This is a basic "chicken-vs.-egg" conundrum. If the autonumber hasn't been assigned yet, you can't offer it, but then this pop-up has no place to find what it wants. Note that if you cannot commit the new record right away, you will NEVER know the correct ID until it is too late. This is why I've called it a design issue. In essence you are asking a question that cannot be answered at that time.

 

[Pat Hartman]

No one has asked why you are using unbound forms. Access is a Rapid Application Development (RAD) tool. Its primary feature is bound forms. If you are not using the most important feature of your tool, you are using the wrong tool. You will be much happier with some platform other than Access.