Parent and subform Linking issue

[ShaunG]

hi Guys, I have a parent form with 2 subforms linked, the parent form is called Job Progress and has a Job No with clients details. 1 of the subforms is order details and the other is variation details, the parent and subforms are linked by job no. I have a button that opens another form called variation entry, with that form I record a new entry into the variation subform. I have to type in the job no everytime I want a new record for the variation to show up on the subform of the parent form, is there away so that when I open the variation entry form it automatically sends to parent form that is open? ( the idea behind this is this will stop anyone making a mistake if they type the wrong job no in).


any help much appreciated

[John_G]

In one place you talk abour job number, but them you talk about order number. Which is it that links the main form and the subforms? The main form shows a job number but no order number, so you could not use order number to link the main form and the Order Details subform. But, the job number is not being populated on the Order Details subform, which suggests to me that the link is not defined correctly. Also, the Order Details subform does show an Order Number, but where does it come from? The variations subform has a field for Order Number but not Job Number, so how is it linked to the main form?

You shouldn't need a separate form to enter variations; if you have the links defined right, you can just enter data straight into the subform.

Can you clarify some of this, please?

[ShaunG]

No not order no - Order details and variation details.... the forms are linked by the job no and does work correctly. I created an entry form for variations and orders to input data to show up on the parent subform which also works correctly, I created these entry forms because the subforms the rows are locked on the parent form so that people cant accidentally change the data... every time the entry form is opened I have to insert the job no so it links to the subform is there away that when the entry form opens it automatically filters the job no and creates a new entry line to input the data to the subform.

[Micron]

Not sure I get it.

I have to type in the job no every time I want a new record for the variation to show up on the subform of the parent form

Type in where?

, is there away so that when I open the variation entry form it automatically sends to parent form that is open?

You just opened it, so it's blank, so send what?
Are you asking for the job number to automatically enter into the variation form when opened?
Make the new variation record show on the main form's subform when the variation form is closed?
The latter is a matter of requerying the subform. The former can be passing the job number to the variation form via its OpenArgs; or setting a variation form control value via main form code before making the variation form visible.

[John_G]

( the idea behind this is this will stop anyone making a mistake if they type the wrong job no in).

So you set it up so they don't have to (or better, so they can't type it in). Micron's suggestion above regarding the Openargs argument of the docmd.openform command.

when I open the variation entry form it automatically sends to parent form that is open?

Not sure what you mean there, but I don't think so. One form cannot send a "message" to another one. However, what you can do is open the data entry form as a modal form, meaning that while it is open, users cannot access anything else, e.g they cannot change information on the main form or any other subform. You open a form as modal by using acDialog as the sixth argument in the DoCmd.OpenForm command.

[ArviLaanemets]

Easiest way to set up subform into master form is to set subform's LinkMasterFields and LinkChildFields properties.

In your case, when your master table Jobs has primary key [Job No], and both tables Order Details and Variations have foreign keys [Job No], then:

After you added subforms into main form, check the properties of both subforms. NB! SUBFORMs, not forms serving as source for those subforms! (When you have in design view main form activated, you make a single click on subform to activate subform. Next click activates a form which is source of subform.)

Activate subfrom's Properties. In both LinkMasterField and LincChildField must be "[Job No]". When it is so, then:
1. Whenever you select another Job in main form, Order Details and Variations linked to main form are displayed in subforms;
2. Whenever you add a new entry into either of subforms, this entry is automatically linked to main form, i.e. into control with [Job No] as source, the value of [Job No] from main form is inserted (and it is better hide the Job No control in subforms from user, so user can't mess up with this).

Of-course when you use some pop-up form to enter new records into subforms, this will not work, and you have to use VBA to make all the work which Access would do for you "for free".

[Micron]

I have a parent form with 2 subforms linked

I took it to mean that what you suggest has already been done. While your suggestion might be applicable here, it seems John_G and I aren't clear on what's needed. The 3 of us seem to be more responsive to the issue than the OP is. I think I will bow out until things are cleared up or at least an attempt is made to do so.

[ShaunG]

I took it to mean that what you suggest has already been done. While your suggestion might be applicable here, it seems John_G and I aren't clear on what's needed. The 3 of us seem to be more responsive to the issue than the OP is. I think I will bow out until things are cleared up or at least an attempt is made to do so.

Apologies for not being able to get back sooner im new at this and takes me a bit.... The main form and subforms I have created do work as would like them to and they are linked by the job no as discussed.
I have changed my order entry form to try give you a better idea, first I search the jobs until I find the correct job I want to update, the example is job no 1111 at the moment the correct order and variation details for the job are displayed and details do change as the Job No is changed.

at the moment when the order entry loads the previous order for the Job No 1111 is shown in the fields and when I pressed new entry all fields are blank. all the fields need to be re-entered.

im hoping to achieve - when order entry form is loaded.... the job No (1111) is filtered in the Job No field and then once I put a value in Row No (2) that will become the Order No (11112) and then I can put data in the remaining fields and then save it.
also on the order entry form im trying to Automatically Calculate the Order No in the Order No Field....... Job No + Row No = Order No

hope this makes sense.
I have a picture but when I click on insert picture my computer shows nothing in the box, like I said new to this guys.

[ShaunG]

Managed to get it...

Even better when I open the Order Entry if it could Filter the Job No Automatically so Job No (1111) and the Row No field filters to the next NEW record so (2) and then the Order No field Calculates the Order No automatically (11112) and then allowing me to add the details for the order would be ideal is it possible?

[John_G]

...at the moment when the order entry loads the previous order for the Job No 1111 is shown in the fields and when I pressed new entry all fields are blank. all the fields need to be re-entered.

That's normal behaviour. Because the Order Entry form is not a linked subform, you have to tell it through VBA what the order number is. You have two "New Entry" buttons on different forms, so how you get the Job Number will be different. Can you post the VBA code behind those two buttons?

Is the Order Entry form bound to a table? How does the "Save Record" button work?

...the job No (1111) is filtered in the Job No field and then once I put a value in Row No (2) that will become the Order No (11112)

That's redundant - the combination of Job Number + Row Number is the order number - is there any particular reason why you need to have it as a distinct field?
It's an easy enough calculation; assuming that Job Number is numeric, then the order number = Job Number * 10 + row Number.
You could put that in to the After Update event of the Row Number, so that users dont have to type it explicitly.

One question: If an existing order record needs to be edited, how do you do it?

[ShaunG]

on the parent form I have 3 Entry Buttons.... Order Entry, Received Goods Entry and Variation Entry - I inserted each button in design view using button feature OpenForm. the reason I had to put a New Entry button on the Order Entry Form so that it would remove the uneditable details of the previous order and I could insert a new order to the Job No.

the 3 Buttons (forms) insert the data back to the Original tables. - Ordered Goods, Received Goods, Variation List

save record button is just myself trying different technique's to get an outcome or trying different macros in the buttons if that makes sense, can be deleted. save and exit works fine.

I have 2 Querys 1 for Ordered Goods and 1 for Received Goods and then I have a UnionForm combining the 2 Querys together which is 1 of the subforms the "Order Details Subform" this is so we can keep track of Orders and when they have been received.... I created it like this so that it is as easy as possible for employees to preview jobs to see how orders are travelling without accidentally changing the records.

the reason for the Order No is I have indexed this field with no duplicates.... as Job No and Row No in the Tables will show up multiple times I can use the Order No for the Combined Query to bring the 2 querys together.

ok no worries ill give that a shot for automatic Order No. cheers. (I cant get it to work.... is that in the expression built or code and would that be on the Order Entry Form or the subform)

im going to create another 3 forms for the editing existing data for owner and myself to have control of only.. but haven't gotten that far yet

[ssanfu]

In the two sub forms, are the "Row No" autonumbers?

If yes, see:
Autonumbers--What they are NOT and What They Are
http://www.utteraccess.com/wiki/Autonumbers

Under "What they are NOT", be sure and read #6 several times..........

[John_G]

New Entry button on the Order Entry Form so that it would remove the uneditable details of the previous order and I could insert a new order to the Job No.

OK. What you can do is assign the job number to the Job Number field on the order entry form when it is first opened, and don't clear it when you do a save. You can prevent users from changing it by setting the "Locked" property to "Yes". If a control (field) is Locked, users can't make changes to it, but VBA can.

save record <snip> can be deleted. save and exit works fine.

No - you'll need it if users are going to enter multiple orders for the same job. You don't want them having to reopen the form for each new order.

One thing you'll need to account for - if a user enters an order, presses "Save Record", and then presses "Save Record" again or presses "Save and Exit", what happens? The save procedure should check that there is data to be saved - you'll have to decide what fields have to be filled in before the record is "Valid" and can be saved.

Actually, the button you don't need is "New Entry". If you clear the data after saving (with the "Save Record" button), then you effectively have a New Entry already.

[ShaunG]

no they aren't.... Our Row No would end up like 12223 or 12224 in no time, if I could make it so that they start fresh at 1 on every Job No I would have set it to autoNo.
as I said im very new to this and code I find hard to get my head wrapped around.

[ssanfu]

no they aren't.... Our Row No would end up like 12223 or 12224 in no time, if I could make it so that they start fresh at 1 on every Job No I would have set it to autoNo.

Great! Sounds like you have code to enter the row no.

Had to ask. Without seeing the dB, can't tell.........


I'll back out and just lurk......