Link Master and Child fields issue

[imrosie]

Hello Experts

This is a hard one (I still speak newbie). An expert may think it's not a big deal, but, I am trying to replace my Main Order form with a subform because of the following:

Original form was based on a query of 2 tables (Order and Customers), the subform was based on table OrderDetail(productname, quantity, discount, unitprice). I was experiencing autonumber problems when attempting to start a new order, because of the inner join of Orders and Customers tables. I then changed the query inner join to a a 'left join'...it worked, but broke the rest of the form....
So I've concluded that I have to figure out how to get the Order data into a new Main Order subform; in which case I would have 2 subforms versus 1.
The problem is I'm not sure how to connect the 'Link Master and Child fields together, so that the two subforms would 'relate' appropriately in processing an Order.

Help with suggestions.
Here is the new Main form's SQL query:

SELECT DISTINCTROW Customers.CustomerID, Customers.FirstName, Customers.LastName, Customers.CompanyName, Customers.BillingAddress, Customers.City, Customers.StateOrProvince, Customers.ZIPCode, Customers.Country, Customers.ContactTitle, Customers.PhoneNumber, Customers.FaxNumber, Customers.Email, Customers.CellNumber

the first subform is tied directly to the Order Details table, so there's no query. It displays in DataSheet mode. Here are the fields:
OrderDetailID
OrderID
ProductID
Quantity
Discount

the second subform is tied to a query(qryOrderDetail), displays in Form View. based on the Orders table. Here are the fields:
OrderID
CustomerID
EmployeeID
OrderDate
PurchaseOrderNo
ShipName, Shipaddress (more ship info fields)

this second subform contains the info that didn't work (in the left join) on the original Main form.

I believe that the Link Master should be on CustomerID, and the Link Child should be on OrderID. Any suggestions or insight would be greatly appreciated. thanks so much.

Rosie

[Jim Doherty]

Hello Experts

This is a hard one (I still speak newbie). An expert may think it's not a big deal, but, I am trying to replace my Main Order form with a subform because of the following:

Original form was based on a query of 2 tables (Order and Customers), the subform was based on table OrderDetail(productname, quantity, discount, unitprice). I was experiencing autonumber problems when attempting to start a new order, because of the inner join of Orders and Customers tables. I then changed the query inner join to a a 'left join'...it worked, but broke the rest of the form....
So I've concluded that I have to figure out how to get the Order data into a new Main Order subform; in which case I would have 2 subforms versus 1.
The problem is I'm not sure how to connect the 'Link Master and Child fields together, so that the two subforms would 'relate' appropriately in processing an Order.

Help with suggestions.
Here is the new Main form's SQL query:

SELECT DISTINCTROW Customers.CustomerID, Customers.FirstName, Customers.LastName, Customers.CompanyName, Customers.BillingAddress, Customers.City, Customers.StateOrProvince, Customers.ZIPCode, Customers.Country, Customers.ContactTitle, Customers.PhoneNumber, Customers.FaxNumber, Customers.Email, Customers.CellNumber

the first subform is tied directly to the Order Details table, so there's no query. It displays in DataSheet mode. Here are the fields:
OrderDetailID
OrderID
ProductID
Quantity
Discount

the second subform is tied to a query(qryOrderDetail), displays in Form View. based on the Orders table. Here are the fields:
OrderID
CustomerID
EmployeeID
OrderDate
PurchaseOrderNo
ShipName, Shipaddress (more ship info fields)

this second subform contains the info that didn't work (in the left join) on the original Main form.

I believe that the Link Master should be on CustomerID, and the Link Child should be on OrderID. Any suggestions or insight would be greatly appreciated. thanks so much.

Rosie

Hi Rosie

If you use the main customer form in obviously single form view and you want to link the Order to that customer and the primary order record is in a subform then the MASTER link is with customerID on the main customer form and the child link is with the Customer ID on the SECOND subform dealing with the order.

Having established that, you can then use the main customer form as a 'Bridge' as it were, to enable the two main elements for the order (namely both of the subforms... to communicate with each other) You achieve this by placing an unbound textbox (hidden) on the main customer form and setting its controlsource to the ORDERID of the second subform you referred to. Name the unbound hidden textbox on the main form txtLinker or something and then on the first subform (the one for ORDER DETAILS) set the MASTER link to the hidden control txtLinker and the child Link to the OrderID on the order details subform.

This ties the two together through an intermediary control if you like, which you should see each time you select a different order in the order subform the order details in its subform will adjust accordingly, if not (maybe you have an ON CURRENT event the size of a house I don't know :))) just see whether txtLinker is being properly refreshed (it should do it automatically but if it is not doing (particularly if you have dozens of controls on the main form) then set txtLinkers value explicitly from the on current event of primary order form. This is one way of doing it another way is to set global variables with the reference numbers needed but given globals can get killed when you get an unexpected runtime error for somethig else I tend not to use that method.

It does work I've used the method myself several times..............on systems I don't rely on hahaha.... only joking)

Hope this helps

Jim

[imrosie]

Wow Jim,

Whoa,,,,,this sounds very detailed, I'm sure you've done this before....I'm restating the steps I believe you've suggested:

1.)Second Subform (tied to Orders) set Link Master & Child fields both set to CustomerID

2.)Place and unbound textbox(hidden) on Main form call it 'txtLinker'; make control source OrderID from second Subform.

2.)First Subform (tied to Order Details table) currently set with Link Master and Child fields set to OrderID. The Link Master should be changed to 'txtLinker' as control source. Leave Link Child = OrderID.

My Current event has only 1 event.
I'm going off to try this now....thanks in advance Jim. I will post back with results for the benefit of others..

[imrosie]

Hi Jim,
It's Rosie,
I'm having a little difficulty with formatting the string for Link Master field on 1st sub.

I set hidden box on Main form as 'txtLinker'; set controlsource as "control source = [trial-Add an Order and Details]!customerorderid...so far no complaints

However, I'm getting a syntax error with the Link Master on 1st subform in attempting to set it to the 'txtLinker control on Main form. Here's the error.
"Syntax error in query expression'([_-[trial-Add an Order and Details]]!txtLinker = OrderID)'

I set Link Master on 1st sub like this:

=[trial-Add an Order and Details]![txtLinker]

As directed, I've left the second Subform's Link Master and Child fields set to CustomerID...no problem.

What am I doing wrong?

By the way, I do have 14 controls on the Main form. My current event is a 1 liner (docmd to disable one on my controls).
So could you give me the manner of setting txtLinkers value explicitly on the current event my Order form. Should I use
a 'set' or 'dim' statement?

set txtLinker = Forms![trial-Order Ship Details Subform]!customerorderid

thanks.....I'm getting there....with your help
Rosie

[Jim Doherty]

Hi Jim,
It's Rosie,
I'm having a little difficulty with formatting the string for Link Master field on 1st sub.

I set hidden box on Main form as 'txtLinker'; set controlsource as "control source = [trial-Add an Order and Details]!customerorderid...so far no complaints

However, I'm getting a syntax error with the Link Master on 1st subform in attempting to set it to the 'txtLinker control on Main form. Here's the error.
"Syntax error in query expression'([_-[trial-Add an Order and Details]]!txtLinker = OrderID)'

I set Link Master on 1st sub like this:

=[trial-Add an Order and Details]![txtLinker]

As directed, I've left the second Subform's Link Master and Child fields set to CustomerID...no problem.

What am I doing wrong?

By the way, I do have 14 controls on the Main form. My current event is a 1 liner (docmd to disable one on my controls).
So could you give me the manner of setting txtLinkers value explicitly on the current event my Order form. Should I use
a 'set' or 'dim' statement?

set txtLinker = Forms![trial-Order Ship Details Subform]!customerorderid

thanks.....I'm getting there....with your help
Rosie

Hi Rosie,

Yes I get somewhat verbose unfortunately at the best of times and don't always articulate concisely what I mean unfortunately.

To deal with this I think what I'm gonna do is replicate this scenario for you in context of the Northwind Traders example database. I know what the field names are the subform names etc and can see the logic on screen here so if you follow me on this, you can do the same on your copy of Northwind and see if it fits your needs.

First

Choose the query called "Orders Qry" quickly use the autoform feature to make a form and save the form name it as subform1

Second

Choose the query called "Order Details Extended" quickly use the autoform feature to make a form and save the form name it as subform2

Three

Open up the form called "Customer" in design view

Four

Drop the above two subforms on the screen (Cancel any wizard help because the wizard has no concept of this method) you just want to drop the subforms on the screen

Five

Drop on the Customers field an unbound textbox call it txtLinker and set its controlsource to

=[Forms]![Customers]![Subform1].Form!OrderID


Six

Go into the properties box for Subform1 and set the following

LinkChildField: CustomerID
LinkMasterFields:CustomerID

Seven

Go into the properties box for Subform2 and set the following

LinkChildField: OrderID
LinkMasterFields:txtLinker

Save the Customers form, close it, reopen it and you will see that txtLinker displays the ORDERID from subform1

Subform2 displays the related records for the ORDERID displayed in txtLinker

This is the BRIDGE I was talking about. Now this does this without any code being entered by you at all.... simply relying on Access ability to refresh its screen controls where the subforms have the ability to keep pace with it so speak...simplistic explanation I know but nonetheless thats the visual effect you see.

Remember theres not much going on in the example customer form by default nothing ON CURRENT or elsewhere behind the form its very lightweight in that context and to that extent. If have successfully replicated what I have done here then this gives you a customer....there orders....and the specific elements of each order in subform2 as you would wish to see when you select each record in the subform1 dataset.

Now then......if the ON CURRENT of the Customers form WAS heavy with code or if the form has loads to deal with in code generally numerous controls various Me.Recalcs scattered here there and everywhere you can imagine what I mean I think.a........... busy form in short........ then look at it this another way.........

You could remove the controlsource of txtLinker so that it does NOT show the value

=[Forms]![Customers]![Subform1].Form!OrderID

You leave txtLinker with nothing at all, no controlsource, completely stateless so to speak pretty useless as is!!.

So HOW then would we get then the ORDERID into txtLinker for it to do its work

In this scenario we would go into the subform1 that deals with ORDERS
and in ITS ON CURRENT event set the value of txtLinker explicitly to the OrderID of subform1 like so

Forms!Customers!txtLinker = Me!OrderID

At which point subform2 will pick up on that visually and return records that match the ORDER

Ahhhhhh we are daisy chaining here in code I here you say!! yes that absolutely correct. How it works for you within the context of the heaviness of your database is for you to test and see obviously.

I am trusting that this shows to you the purpose of what I intended rather than me trying to rectify something you are doing without knowing the actual naming conventions and overall syntax you are using. If you follow my example and apply it to yours should see AS IS whether your database responds in the same way. If it doesn't then its back to square one!

Hope this helps

Jim

[imrosie]

Hi Rosie,

Yes I get somewhat verbose unfortunately at the best of times and don't always articulate concisely what I mean unfortunately.

To deal with this I think what I'm gonna do is replicate this scenario for you in context of the Northwind Traders example database. I know what the field names are the subform names etc and can see the logic on screen here so if you follow me on this, you can do the same on your copy of Northwind and see if it fits your needs.

First

Choose the query called "Orders Qry" quickly use the autoform feature to make a form and save the form name it as subform1

Second

Choose the query called "Order Details Extended" quickly use the autoform feature to make a form and save the form name it as subform2

Three

Open up the form called "Customer" in design view

Four

Drop the above two subforms on the screen (Cancel any wizard help because the wizard has no concept of this method) you just want to drop the subforms on the screen

Five

Drop on the Customers field an unbound textbox call it txtLinker and set its controlsource to

=[Forms]![Customers]![Subform1].Form!OrderID


Six

Go into the properties box for Subform1 and set the following

LinkChildField: CustomerID
LinkMasterFields:CustomerID

Seven

Go into the properties box for Subform2 and set the following

LinkChildField: OrderID
LinkMasterFields:txtLinker

Save the Customers form, close it, reopen it and you will see that txtLinker displays the ORDERID from subform1

Subform2 displays the related records for the ORDERID displayed in txtLinker

This is the BRIDGE I was talking about. Now this does this without any code being entered by you at all.... simply relying on Access ability to refresh its screen controls where the subforms have the ability to keep pace with it so speak...simplistic explanation I know but nonetheless thats the visual effect you see.

Remember theres not much going on in the example customer form by default nothing ON CURRENT or elsewhere behind the form its very lightweight in that context and to that extent. If have successfully replicated what I have done here then this gives you a customer....there orders....and the specific elements of each order in subform2 as you would wish to see when you select each record in the subform1 dataset.

Now then......if the ON CURRENT of the Customers form WAS heavy with code or if the form has loads to deal with in code generally numerous controls various Me.Recalcs scattered here there and everywhere you can imagine what I mean I think.a........... busy form in short........ then look at it this another way.........

You could remove the controlsource of txtLinker so that it does NOT show the value

=[Forms]![Customers]![Subform1].Form!OrderID

You leave txtLinker with nothing at all, no controlsource, completely stateless so to speak pretty useless as is!!.

So HOW then would we get then the ORDERID into txtLinker for it to do its work

In this scenario we would go into the subform1 that deals with ORDERS
and in ITS ON CURRENT event set the value of txtLinker explicitly to the OrderID of subform1 like so

Forms!Customers!txtLinker = Me!OrderID

At which point subform2 will pick up on that visually and return records that match the ORDER

Ahhhhhh we are daisy chaining here in code I here you say!! yes that absolutely correct. How it works for you within the context of the heaviness of your database is for you to test and see obviously.

I am trusting that this shows to you the purpose of what I intended rather than me trying to rectify something you are doing without knowing the actual naming conventions and overall syntax you are using. If you follow my example and apply it to yours should see AS IS whether your database responds in the same way. If it doesn't then its back to square one!

Hope this helps

Jim

Jim,
Hi, no you're not overly verbose....as a Newbie, I need all the clear explanations I can get....so very verbose works great for me...
In fact I absolutely love the way you talk techie to a Newbie....I actually understand what you're talking about...You must have been an instructor at some point in your career....(I used to teach Unix Admin's so I can relate). I'm going now to do exactly what you said...I'll be right back with the results.


btw, I hope it works too.....I don't want to be back to square one!
thanks Jim

[imrosie]

Jim,

By goodness, it's working great...It appears that the 'Bridge',,,yes the daisychaining, works.

Now I need to ask a couple more questions regarding passing variable data. I used to pass customer info to the shipping controls.

Can I still do that as though the subforms and Main forms are melded into one as long as I use this format?

=[Forms]![FormName]![Subform1].Form!VariableName


Secondly, I had several cmd buttons on my Main form originally, (beginneworder, clear form and undo changes) they're showing errors because of the new form design. Should those be working automatically or do I have to go into those now and point them to the appropriate subform? I suspect so.

Finally, I have (had on original) controls for Subtotals, Salestax and OrderTotals, again those were set to show the info from the original Subform (OrderDetails)......should I expect those be working automatically?

Another Original cmd button that Previewed a Customer Order (based on the OrderID) prior to printing an Invoice Report. When I tried that cmd button, it says, it doesn't know where customerorderid is???

Here's what it looked like originally (Orders by Customer query is filtered by OrderID, controlname is customerorderid):

DoCmd.OpenReport "Orders by Customer", AcView.acViewPreview

I'm sorry if I'm bombarding you with questions. I really need those to work on the Main form. Should I be using the above format? thanks
Rosie

[Jim Doherty]

Jim,

By goodness, it's working great...It appears that the 'Bridge',,,yes the daisychaining, works.

Now I need to ask a couple more questions regarding passing variable data. I used to pass customer info to the shipping controls.

Can I still do that as though the subforms and Main forms are melded into one as long as I use this format?

=[Forms]![FormName]![Subform1].Form!VariableName


Secondly, I had several cmd buttons on my Main form originally, (beginneworder, clear form and undo changes) they're showing errors because of the new form design. Should those be working automatically or do I have to go into those now and point them to the appropriate subform? I suspect so.

Finally, I have (had on original) controls for Subtotals, Salestax and OrderTotals, again those were set to show the info from the original Subform (OrderDetails)......should I expect those be working automatically?

Another Original cmd button that Previewed a Customer Order (based on the OrderID) prior to printing an Invoice Report. When I tried that cmd button, it says, it doesn't know where customerorderid is???

Here's what it looked like originally (Orders by Customer query is filtered by OrderID, controlname is customerorderid):

DoCmd.OpenReport "Orders by Customer", AcView.acViewPreview

I'm sorry if I'm bombarding you with questions. I really need those to work on the Main form. Should I be using the above format? thanks
Rosie

Can I still do that as though the subforms and Main forms are melded into one as long as I use this format?

Yes you can just remember that the subform is SUB so its subordinate in the hierarchy of things

=[Forms]![FormName]![Subform1].Form!VariableName

is nearly right but by variable I assume you got mixed up and mean controlname for variable name


Secondly, I had several cmd buttons on my Main form originally, (beginneworder, clear form and undo changes) they're showing errors because of the new form design. Should those be working automatically or do I have to go into those now and point them to the appropriate subform? I suspect so

You are right... repoint to subforms if the commands need to reference controls that reside on those subforms


Finally, I have (had on original) controls for Subtotals, Salestax and OrderTotals, again those were set to show the info from the original Subform (OrderDetails)......should I expect those be working automatically?

Not if you have made changes that effect them rebuild them remember you have mounted subforms on the Customers form so Customers is the parent form and your previous subform was mounted on a different form? if I have read you right?


Another Original cmd button that Previewed a Customer Order (based on the OrderID) prior to printing an Invoice Report. When I tried that cmd button, it says, it doesn't know where customerorderid is???

I'm not surprised if you have redesigned. If you are going with your customer for as the main form then customer id will be on the subform subordinate to main form customers.... whatever path you have now given it ie frmCustomers!Subform.form!CustomerOrderID I don't know your specific naming conventions Rosie

DoCmd.OpenReport "Orders by Customer", AcView.acViewPreview[i]

This command merely opens the report the report. The report is looking for the parameter customerid from a location set by the previous design which again if you have altered it, and it currently sits on a subform on Customers main form them the query or SQL syntax the report is reliant on requires amending, to point it to the current location of the customerid which if I am right, once again, is on the subform sitting on the customer main form.

Maaann this is like juggling a plate of spaghetti hahahahaha bring onnn the meatballs.

Hope this helps?

Regards
Jim

[imrosie]

Can I still do that as though the subforms and Main forms are melded into one as long as I use this format?

Yes you can just remember that the subform is SUB so its subordinate in the hierarchy of things

=[Forms]![FormName]![Subform1].Form!VariableName

is nearly right but by variable I assume you got mixed up and mean controlname for variable name


Secondly, I had several cmd buttons on my Main form originally, (beginneworder, clear form and undo changes) they're showing errors because of the new form design. Should those be working automatically or do I have to go into those now and point them to the appropriate subform? I suspect so

You are right... repoint to subforms if the commands need to reference controls that reside on those subforms


Finally, I have (had on original) controls for Subtotals, Salestax and OrderTotals, again those were set to show the info from the original Subform (OrderDetails)......should I expect those be working automatically?

Not if you have made changes that effect them rebuild them remember you have mounted subforms on the Customers form so Customers is the parent form and your previous subform was mounted on a different form? if I have read you right?


Another Original cmd button that Previewed a Customer Order (based on the OrderID) prior to printing an Invoice Report. When I tried that cmd button, it says, it doesn't know where customerorderid is???

I'm not surprised if you have redesigned. If you are going with your customer for as the main form then customer id will be on the subform subordinate to main form customers.... whatever path you have now given it ie frmCustomers!Subform.form!CustomerOrderID I don't know your specific naming conventions Rosie

DoCmd.OpenReport "Orders by Customer", AcView.acViewPreview[i]

This command merely opens the report the report. The report is looking for the parameter customerid from a location set by the previous design which again if you have altered it, and it currently sits on a subform on Customers main form them the query or SQL syntax the report is reliant on requires amending, to point it to the current location of the customerid which if I am right, once again, is on the subform sitting on the customer main form.

Maaann this is like juggling a plate of spaghetti hahahahaha bring onnn the meatballs.

Hope this helps?

Regards
Jim

Jim, you're hilarious....anyway, here are some meatballs Jim......

Yes I got mixed up, meant controlname, not variable.

As for Subtotal, Salestax and OrderTotals, now it's working....I didn't change any naming scheme inthat respect.

I've tried this to format the DoCmd.OpenReport, but it's not working,,,,,so I must be formatting the filter wrong. any suggestions? Yes, the subform with customerorderid is actually called 'Order Ship Details Subform'...

DoCmd.OpenReport ("Orders by Customer"), AcView.acViewPreview, (Forms![Order Ship Details Subform].Form.customerorderid)

Lastly, I tried fixing a cmd button (the one for 'beginanorder'). It's purpose is to clear out the OrderDetail controls(unitprice, quantity, discount, productid, etc)., once a customer is pulled up to begin a new order.

I used this as a format,,,,but compiler doesn't like the format:

Forms![Order Details Subform].Form.ProductID = Undo

any ideas?

thanks
Rosie

[MGrowneyARSI]

It would be alot simpler if you go into the query for your report and direct the critiria to the field on the subform then all you have to do is call the report
=[Forms]![Name_Of_Form]![Name_Of_Sub_Form]![Name_Of_Field] as for the undo I'm not sure what your trying to do but it sounds like your trying to clear fields so that a new record can be made 1. I would say just put an add new rec but it seems you need to clear the fields so I guess the way I whould go about it would be to create a sub call it clear_Fields or somthing along those lines and just do me.Name_Of_Field = Null for each one you need cleared Hope I helped at least a little You had alot going on there good luck

[imrosie]

It would be alot simpler if you go into the query for your report and direct the critiria to the field on the subform then all you have to do is call the report

=[Forms]![Name_Of_Form]![Name_Of_Sub_Form]![Name_Of_Field] 

as for the undo I'm not sure what your trying to do but it sounds like your trying to clear fields so that a new record can be made 1. I would say just put an add new rec but it seems you need to clear the fields so I guess the way I whould go about it would be to create a sub call it clear_Fields or somthing along those lines and just do me.Name_Of_Field = Null for each one you need cleared Hope I helped at least a little You had alot going on there good luck

Hi MGrowneyARS,

Yes it probably would, if I knew how. I'll try to figure that one out. The 'UNDO'I just need to clear those fields in a sub called 'beginanorder' on click, like so:

Private Sub beginanorder_Click()
[Forms]![Add an Order and Details]![Order Details Subform]![ProductID] = Null 

I followed your example...I get the run-time error '438' property doesn't support this property or object....HuH?
Rosie..

[MGrowneyARSI]

Was this on runnign the report or clearing the fields?
Sorry for the wiat