Transferring data from one table, to another.

[Drak]

Hello. I tried searching the forums for an answer and I found similar issues, but I still can't quite get it. Attached is my current example database (It's small).


I have a form that allows you to lookup / change / add customers.

On this form, I have a button "New Repair" that opens another form (New Repair) where I enter the name, and the status of the repair.

My issue is how do I send the CID (the company id in the table) from the one form (Customer List), to the second one (New Repair). Then with the CID, I would like todo a query to the customer database and retrieve the company name based on the CID, and display it in the header.

I have the tables laid out with an example customer.
Thanks!

[June7]

Basing CID primary key on first six letters of name can cause issue. Eventually, likely will encounter two names that start same. Suggest design a different unique ID scheme or use Autonumber datatype.

Instead of opening New Repair from Customers form, set up so open New Repair form directly and select company in combobox on that form. Include the Customers table in the record source by joining on CID fields. This way the related customer info will be available for display in textboxes. Lock those textboxes so values cannot be edited on this form.

Or, put the New Repair form as a subform on Customer List form. Maybe use a tab control to organize the controls. Customer info on one tab, New Repair subform on another tab.

Anything else will involve VBA code.

[hertfordkc]

I have access 2007 and can't open your db. Ive done essentially what you want to do as follows.
When the button is clicked, the buttons on click event runs a VBA procedure or a macro which opens the second form and makes the first form invisible. The second forms on open event can look at the form 1 value and insert it into a filter. You'll need to do a requery.
If you don't need to return to the 1st form, the on click macro/procedure could store the value in a global variable.
This would not require a reference to the first form, so it could be closed after the on click event.
Tell me what needs more detail.

[Drak]

How would I go about doing it in VBA? Use the "onclick" procedure, then just run a query asking the table for a company based on the id?

[hertfordkc]

The 'on click' procedure would, at a minimum, open the 2nd form. If the first form is no longer needed, then the I'd would need to be assigned to a global variable before opening the 2nd form. After issuing 'open form', the 1st form could close.
The 2nd form would be based on a query without criteria for Id. The form filter would reference the global variable, Id, or reference form1's control value for the id. You need to check the form properties to be sure that the filter is applied.
You can also generate a filter during the 2nd forms 'onopen' event, but doing so will require a 'requery'.
My Access PC isn't available today. I apologize for not being able to generate a working example.

[June7]

Several ways to do this. You can have code in the click event of main form or in the Open event of New Repair.

In click event after line to open form, set value of the field on New Repair:
Forms![New Repair]!CID = Me.CID

Or use the OpenArgs argument of DoCmd.OpenForm
DoCmd.OpenForm "[New Repair]", , , , acAddNew , , Me.CID
Then in Open event
If Not IsNull(Me.OpenArgs) Then Me.CID = Me.OpenArgs

I just thought of another way that does not require VBA but I have never applied it. You can set Default value property of the CID field on New Repair form to the field on main form. If the field is null when record is opened, the default will apply, since a new record would be null, this might work for you. The Default parameter would be: Forms!formname!CID.

[Drak]

Several ways to do this. You can have code in the click event of main form or in the Open event of New Repair.

In click event after line to open form, set value of the field on New Repair:
Forms![New Repair]!CID = Me.CID

Or use the OpenArgs argument of DoCmd.OpenForm
DoCmd.OpenForm "[New Repair]", , , , acAddNew , , Me.CID
Then in Open event
If Not IsNull(Me.OpenArgs) Then Me.CID = Me.OpenArgs

I just thought of another way that does not require VBA but I have never applied it. You can set Default value property of the CID field on New Repair form to the field on main form. If the field is null when record is opened, the default will apply, since a new record would be null, this might work for you. The Default parameter would be: Forms!formname!CID.

Thanks for the info. Once I got the CID value on the new repair form, how do I grab the company name that relates to the saved CID, in the table, and show it on the new repairs form?

Sorry for the newbie question. Thanks again.

[June7]

One way is to simply include the company table in the form's recordsource by joining the two tables. The jointype would be like 'show all records from repair table and only those from customers that match'. This is a left or right join (I always forget which) but not an inner join. This will make the related customer information available for display in textboxes. Set these textboxes as Locked Yes TabStop No so cannot be edited on this form.

Another way to show related records is form/subform arrangement. Main form bound to customers and subform bound to repairs.

[Drak]

I linked the two tables toghter, and using DLookUp:

Private Sub Form_Load()
    Dim Company As Variant
    Company = DLookup("[Company]", "Customers", "[CID] =" & Me.CID)
    TitleTest = Company
End Sub

Which "seems" to work but VB throws this error at me:
"The expression you entered as a query parameter produced this error: 'ABM'"

"ABM" is the correct CID I'm trying to pull it's Company Name from the "Customers" table.

[June7]

There is no reason to use the DLookup when tables are joined in the form's RecordSource. Also, the DLookup could be in textbox ControlSource. No need for VBA.