Linking form fields

[rev_ollie]

Hi



Can anyone help with a solution to linking form fields?
I want it so that when the users click a button on my main form (Orders) a second input form (Order Details) pops up with the Order ID field allready populated with the same value on the Orders form.

I.e If the Order ID on Orders = 1 When you click the button the field on Order Details also = 1

I have tired linking it with an expression:
[forms]![Orders]![Order ID]

but it just wont take a the value.
I also tried a query to fidn the result of the Order ID field but that didn't work, so either thats not an appropriate solution or I did i wrong (Most probable )

Currently I have it set as a combo box with a drop down that has all the order ID's in but I always think its better if the user does as little as possible so there is less chance of anything going wrong.

Any Ideas?

[Evgeny]

Use vba as follow: onClick event of the button that opens your second form:

DoCmd.OpenForm "Nameofyoursecondform, , , "OrderID = " & Me.OrderID ...

OderID is the name of your field on the second form

Me.OrderID is the name of your field on the first form..

If you have trouble applying this method just post a sample for me to fix.

Regards

[rev_ollie]

Thanks for the reply Evgeny!

I applied the code to the button but it appears there is a syntax error. It just goes red and then says expected end of statement.

I have attached the file if you wouldn't mind taking a quick look.
Its on the button "Add Items" on the form "Orders". Its meant to open the form "Order Details Input" and link Order ID.

Thanks

[Evgeny]

no worries, i had hard time with vba syntax errors when i first started using it You will get used to it.... By the way ... the file you attached is empty lol. Can you please reattach it again

[rev_ollie]

Oh dear lord
This one should have a file in it

Be prepared for a major mess, I don't know what I've done that's right or wrong

[Evgeny]

ok, i will go through the mess when i have a minute

[maximus]

This is what I understand what you need:
1) you have a order form.
2) you have a order sub-form.
3) you have a button on the order main form when you click on it sub-form opens and a Field on the sub-form is populated with the value of Order Id. This is what I have done.

Private Sub Command6_Click()
On Error GoTo Err_Command6_Click
DoCmd.OpenForm "Sub_order", , , , acFormAdd
Forms!Sub_order!Order_Link = Me.OrdersID

Exit_Command6_Click:
Exit Sub

Err_Command6_Click:
MsgBox Err.Description
Resume Exit_Command6_Click

End Sub

This button on the order form opens Sub_order form and populates the Link_id on the Sub_order form with Order_ID on the Order form.

Note that I have set the sub_order form to open in acFormAdd mode this ensures that your Sub_order form opens blank all the time.

attaching a sample mdb for your reference if tis solves your problem mark the thread solved.

How to use the sample:

type a date in the order form to generate the order id
Click the command button to open Sub-order form.

[Evgeny]

Thank you for your solution.... have not seen this method yet.
if you don't mind, can you please explain the code

Private Sub Command6_Click()
On Error GoTo Err_Command6_Click
DoCmd.OpenForm "Sub_order", , , , acFormAdd
Forms!Sub_order!Order_Link = Me.OrdersID

Exit_Command6_Click:
Exit Sub

Err_Command6_Click:
MsgBox Err.Description
Resume Exit_Command6_Click

I am learning Vba, so i would rather understand what every piece of code does, rather than simply copying and pasting the solution

[maximus]

DoCmd.OpenForm "Sub_order", , , , acFormAdd

This part of the code opens the form Sub_Order in the acFormAdd mode which is a Blank Form the other modes are :

acEdit
acFormPropetySettings
acFormReadOnly



This Line popualtes the the Order_Link Field on the Sub_Order Form with the Value of OrdersID on the Order Form.

It is a good habit to refer form conrols like Forms!Form Name!Control Name. But when refering to a control within the same form you can use Me.ControlName

Forms!Sub_order!Order_Link = Me.OrdersID

I appreciate your effort of learning Vba best of luck!!!!! your are most welcome to enquire anything I will try to help out to the best of my ability.

[rev_ollie]

Oh thanks!
Thats just what I needed.

I'm still getting a compile error however.
I altered the code with my value names and get this:

Private Sub Command144_Click()
On Error GoTo Err_Command144_Click
DoCmd.OpenForm "Order Details Input", , , , acFormAdd
Forms!Order Details Input!Order ID = Me.Order ID

Exit_Command144_Click:
Exit Sub

Err_Command144_Click:
MsgBox Err.Description
Resume Exit_Command144_Click

End Sub

I keep getting a syntax error "Expect =" on this line:
Forms!Order Details Input!Order ID = Me.Order ID

I'm not sure why. I'm sure the names are right as the main form is called Orders and the sub form is called Order Details Input.
Then I'm copying the field Order ID to Order ID on the sub form.

Have I done something wrong there?

[maximus]

Forms![Order Details Input]![Order ID] = Me.[Order ID]

I think this should do

[maximus]

avoid putting space in the names of tables, querys, Forms, reports and controls.

[rev_ollie]

Maximus,
You are a credit to this forum. Once again you have solved my little problems that plague my Access Life.

Now when I finally get the https://www.accessforums.net/queries/total-field-access-5466.html#post19967]queries working[/url] I can get this database off my hands

Also thanks for the naming rules.
I had no idea that spaces caused trouble. I thought they maybe weren't perfect but will try harder next time.

Thanks for your help to Evgeny its nice to know there is help around when you need it. Sorry my Database was so messy

[Evgeny]

Good stuff, glad you got it sort it plus i have learn something new too . Maximus your contribution is much appreciated, keep up a good work

[Lotus49]

Just a quick thank you to the OP and Maximus for this topic.

This problem has been driving me crazy all day, and this was the first post I could make sense of, with my limited understanding.

Graham