Create new record in different table - Junction table

[jamierbooth]

Hi world, (MS access 2000 user.....)

I'm trying to create an "Add" button on a form that will create a new record in a junction table and copy 2 values from the form into new record.

There is a clients table, a contacts table and a junction table - a many to many type relationship connecting the clients and contacts together. As recommended, I've created a junction table with its own Junction ID, so I can pair the Customer ID and the Contacts ID together on each new record (and ultimately see which contacts are related to any selected client, and vice versa).

The code I've looked up from various places isn't working for one reason or another, and I'm not knowledgable enough with VBA to be able to read what I've got wrong.

With the following I get a Compile Error: User-defined type not defined, and the DAO.Database line is highlighted.

Private Sub cmdAddtoClient_Click()
            
Dim HADB_Construction As DAO.Database
Dim rsttbljtnClientsContacts As DAO.Recordset

Set HADB_Construction = CurrentDb
Set rsttbljtnClientsContacts = HADB_Construction.openrecordset("tbljtnClientsContacts")

    rsttbljtnClientsContacts.AddNew
    rsttbljtnClientsContacts!CustID = Forms!frmContactSearchResults!CustID
    rsttbljtnClientsContacts!ContactID = Forms!frmContactSearchResults!sbfrmContactSearchResults!ContactID
    rsttbljtnClientsContacts.Update
 
End Sub

And other variations taken from here and there...
http://www.functionx.com/vbaccess2003/howto/addrecord.htm

http://msdn.microsoft.com/en-us/library/office/bb243801(v=office.12).aspx

The form and button calling this is on each record of a tabular sub-form which lists contacts matching the users search results. The ContactID is contained in each record line here and the CustID is from an unbound field on the parent form.

3x tables are
tblClients
tblContacts
tbljtnClientsContacts

3x fields in the junction table are
JunctionID (PK and autonumber)
CustID
ContactID

Thanks guys!!

 

[pbaldy]

In 2000 you would need to go into Tools/References and check the MS DAO 3.6 reference.

 

[jamierbooth]

Brilliant. OK. Would never have known that in a million years. Code and button work fine now, thanks a lot. And thanks for the various other bits you have taught me on your website!

 

[pbaldy]

Happy to help!