transfer company name from one form to another form

[peterjb44]

Hi All,

been trying to figure this out..

i would like Company name from current open CustomerF form Company field to go in EquipmentF form CompanyID field once i press add equipment button on CustomerF form.....i would also like it to be a new record

i have done this so far...but is not working the way i would like

private sub addequipmentbutton_click()
docmd.openform "EquipmentF",,,"company = '" & company & "'",,acDialog
end sub

what i have noticed

if the company has equipment added to my equipment table then when i click add equipment button on the current CustomerF form the equipment form has the correct company name, but it show the first record.

if its a new customer i am adding details for or a company with no equipment and i go to enter their equipment, then click add equipment button then the EquipmentF form opens as a new blank record with no company name

is there a way to sort this the way i would like it to work?




thanks

peter

[June7]

Should not save company name, just company ID.

Why not use a form/subform arrangement?

What is the database structure (tables and relationships)?

[peterjb44]

HI June7,


thanks for your reply

both Company name (CustomerT table) and CompanyID (EquipmentT table) should be saved

not sure what you mean with form/subform arrangment......pretty much new to this

the two tables are CustomerT and EquipmentT
Company is a field in CustomerT which is related to CompanyID (field) of EquipmentT 1 to many

thanks

peter

[June7]

Review http://office.microsoft.com/en-us/ac...010098674.aspx

[peterjb44]

yes i have a form like that with company name and address etc at the top and a list of their equipment below, but on my CustomerF form this has much more details on it, just thought it would be nice to be able to add equipment directly from the CustomerF form after that was filled in

can this be done

[peterjb44]

sorry my OP code should of been

docmd.openform "EquipmentF",,,"companyid = '" & company & "'",,acDialog

[June7]

If you are adding a new customer and their associated equipment, a form/subform is the easiest approach. Otherwise, will require VBA code.

[peterjb44]

ok, i have no idea how to do that, any ideas?

[June7]

How to do what - VBA? The code will involve:

1. Saving the new customer record.
DoCmd.RunCommand acCmdSaveRecord

2. Opening the equipment form and passing the new customer ID
DoCmd.OpenForm "Equipment", , , ,acFormAdd, , Me.CustomerID

3. Populating the Equipment form foreign key customer ID field with the passed value
Sub Form_Current()
If IsNull(Me.CompanyID) Then
Me.CompanyID = Me.OpenArgs
End If
End Sub

[peterjb44]

Thanks June7

i had to change the end bit of no2 from customerid to company as i was getting a number

Thanks again your great

peter