form with sub form auto number

[PWG]

I have a form JSA that contains a JSA number 1234.
I have a sub form called Hot work. That a a perrant and child link feilds are the JSA Number.
I have used the following in the query to return the value for the Hot Work number of 1234.1. HWnumber:[JSANumber]+0.1. and this works fine in my sub form.
The problem is that if I want to attach another Hot Work To the JSA it returns the same Number of 1234.1.
How can I get The second Number to be 1234.2

 

[arnelgp]

I would add another numeric field (HWNumber) for the subform table so it can get saved.

on the beforeInsert event of your form, generate the number:

private sub form_beforeinsert(cancel as integer)
me.txtHWNumber = Nz(dmax("HWNumber", "subformTableName", "JSANumber='" & Me.Parent!JSANumber & "'"), 0) + 1
end sub

 

[jdraw]

In your database it seems that
1 JSA may refer to 1 or Many HotWorks

If you have JSANumber as PK of JSA table, and HotID as PK of the HotWorks table,
you set JSANumber as a FK in HotWorks. Then in your form subform construct with
JSANumber as the linking field, you will have JSANumber & HotID as the identifier of the combination.

I think there is an issue with your approach of adding +.1 to the parent(JSA Number).
You would have to find the largest HotID for that parent and Add .1 to that in your set up.

Is it that the .1, then .2 is important or just that you can uniquely identify 1 HotWork for this parent from another?

Note:

Just saw arnelgp's response and it seems to do what you asked. Not sure where the "." is accounted for in his code, but that would be a minor adjustment.

 

[PWG]

I get a compile error Method or data member not found. It hi lights .txtHWNumber =

 

[jdraw]

The me.txtHWNumber would refer to a textbox control on the subform.

 

[PWG]

Thank I had put it on the sub form not the main form.
It now just returns me a 0 value for all the HWnumbers

 

[jdraw]

Can you post a copy of the database?

 

[PWG]

In your database it seems that
1 JSA may refer to 1 or Many HotWorks

The JSA can have many Hotwork permits attached to it.
On the job site the contractor carries the HW permit that needs to be associated to the JSA with out the need for a computer to link them for the inspector so he can look at the paper form for the JSA. So the easy way is to use the JSA number and add .1,.2.

 

[PWG]

Its not much and should be simple.
It will used to store JSA numbers and related Hot work and working at Height permit numbers. The form Im trying to use us the JSAFORM

 

[arnelgp]

check the modified table structure.
check out the form and the code how to
generate the Job Safety Analysis, Hot Work Permit and Working @ Height permit.

where is your project?