How do I create a record from two different tables on the same form?

[StarryNight]

Hi Everyone,
I have a form (Access 2000) with text and combo boxes bound to a table showing information about our contracts.

There is also a list box in this form querying client names from another table.

My goal is to have a record of a contract showing, then to highlight a client from the queried list box so i can ultimately click a button and add the contract number and the client name to a table which stores only that information (contract number & clientID).

I have created tables called: Contracts, Clients, ClientContractJunction. The last table has a many to many relationship with the Clients and Contracts tables (as there will be contracts with many clients and clients attached to many contracts)

Can anyone offer a direction in which to take this problem? I'm assuming I need a command button with some code to add the selected information to a record in the ClientContractJunction table. Unfortunately the code is above my skill level - but I'm determined to learn how to do it :-).

[NeoPa]

If you had a Contract maintenance form you could add an unbound ComboBox populated by the [Clients] table. The trigger for the code to create a record matching the link in your [ClientContractJunction] table would be a Command button.

Depending on your preference, you could add the record using VBA & recordsets, or you could knock up some SQL to execute.

[StarryNight]

Thanks NeoPa,
I created an append query which will run when a command button on the form CONTRACTS is clicked. Unfortunately it gives me a message that "i'm about to append 0 rows". Here is the SQL statement. If anyone has any ideas please let me know. I put in the WHERE statement but i'm not sure if that's right.

INSERT INTO clientContractJunction
           ([Contract Number],
            ClientID)
 
SELECT [Contracts].[Contract Number],
       [Clients].[ClientID]
 
FROM  Clients INNER JOIN
     (Contracts INNER JOIN clientContractJunction
  ON ([Contracts].[ClientID]=[clientContractJunction].[ClientID])
 AND ([Contracts].[Contract Number]=[clientContractJunction].[Contract Number]))
  ON ([Contracts].[ClientID]=[Clients].[ClientID])
 AND ([Clients].[ClientID]=[clientContractJunction].[ClientID])
 
WHERE (([Contracts].[Contract Number]=[Forms]![Contracts]![Contract Number])
  AND  ([Clients].[ClientID]=[Forms]![Contracts]![lstSource]));

[NeoPa]

Is the SQL coded into a QueryDef or do you execute it from a string in your code?

I would focus primarily on what values are in your [Contract Number] and [lstSource] controls at the time the button is clicked. Particularly if you may be using Nulls (no data entered).

[StarryNight]

agreed... my goal is for this query to run from a command button on the form.

The contract number field on this form displays the number for a given record and the lstSource list box does display the query of all the Clients' names. In order to attach client names with a contract i thought i would be fairly straightforward to click on a name in the lstSource and add the client name and the contract number in that field to a third table (called ClientContractJunction) when the command button was clicked to run the append query. Judging by the fact that it says '0 rows will be appended' it seems that Access is not seeing the the record of the contract number displayed or the client name or both. I'm obviously missing a key part but I'm not sure what it is. Thanks for your help so far!

here is another SQL code slightly more cleaned up (I hope):

INSERT INTO clientContractJunction ( [Contract Number], ClientID )
SELECT Contracts.[Contract Number], Clients.ClientID
FROM Clients INNER JOIN Contracts ON Clients.ClientID = Contracts.ClientID
WHERE (((Contracts.[Contract Number])=[Forms]![Contracts]![Contract Number]) AND ((Clients.ClientID)=[forms]![Contracts]![lstSource]));

[NeoPa]

The SQL you've got seems fine to me.

The debugging of this is going to be mainly hands on I'm afraid as it is all about the actual data used (which I don't have and would find difficult to comprehend in isolation even if some were posted).

What I can do (possibly the last of any help) is to suggest that in your VBA code you can display the two controls at the time you are about to execute the SQL. Something like :

strMsg = "[Forms]![Contracts]![Contract Number] = '%N'" & VbCrLf & _
         "[Forms]![Contracts]![lstSource] = '%S'"
strMsg = Replace(strMsg, "%N", [Forms]![Contracts]![Contract Number])
strMsg = Replace(strMsg, "%S", [Forms]![Contracts]![lstSource])
Call MsgBox(strMsg)
'Execute your SQL here

Just seeing what's displayed is often enough to ring bells.

I hope this sorts things out for you.

[StarryNight]

through some trial and error I managed to figure out that the inner join was the problem. Once I got rid of that link. It worked just fine. here's the querry. Thanks a huge amount for your help!

INSERT INTO clientContractJunction ( [Contract Number], ClientID )
SELECT Contracts.[Contract Number], Clients.ClientID
FROM Clients, Contracts
WHERE (((Contracts.[Contract Number])=[Forms]![Contracts]![Contract Number]) AND ((Clients.ClientID)=[forms]![Contracts]![lstSource]));
 

[NeoPa]

I'm just sorry I couldn't help more, but very pleased you were able to resolve your problem yourself, even without much help.

Welcome to Bytes!