form with two linked subforms (new record problem) 2

[vdzr]

hi,

maybe you can solve my problem. I have a form(clients) with primClientID and putted two subforms in it, both linked to the (client)form primClientID.
In de clientform i look for a persons name and if it's found,subform1 shows me all his contracts with the possibility to add records in the Continuous subform1.
Continuous Subform2 shows the subcontracts from each selected contract in subform1.
subform2source = query based on a recordcheckboxfield'view' in subform1).
When i click on a contract and add a new subcontract the contractID from subform1 is copy't to subform2 as reference dependend on the recordselector from subform1.

Problem : when i start a new subcontract in subform2 and subform1 selectionrecord is on a new record, I can't change it to the last record subform1 because the uniek key from subform2 is empty.

Private Sub Form_BeforeInsert(Cancel As Integer)
If IsNull(Forms!Clientform!subform1!PrimClientID) Then
MsgBox "Select first a contract", vbOKOnly, "message"
'recordselector on last record subform1 and continue adding record subform2
Else
duplcontractID = Forms!Clientform!subform1!contractID
End If
End Sub

I tried a second solution :

Private Sub Subform1_Current()
If NewRecord Then
Forms![formClienten]![subform2].Form.AllowAdditions = False
Else
Forms![formClienten]![subform2].Form.AllowAdditions = True
End Sub
I get a error (he can't find the object) on load but when i end the debugger the funktion works perfectly. Me.forms ... isn't a solution and ...[subform2].subForm... neather

I gess my (sub)formstring isn't right!
This is not a form with a subform in a subform because then i would only see 'one' contract with several subcontracts (and thats easy done)
thanks already

 

[Remou]

You are missing the word 'form':

IsNull(Forms!Clientform!subform1.Form!PrimClientID

Make sure that subform1 is the name of the subform control, not the name of the form in the control.

Here is more information:

http://www.mvps.org/access/forms/frm0031.htm

 

[vdzr]

it's still not working.

getting de value from subform1 is ok.
1)How can I change the record selector in subform1 to the last record (when new record was selected) when I make a new record in subform2?

2)Is there a code for Esc(aping) a new record? undo isn't working

3)Why is de second solution working but gives an error on load or is there an better solution?

 

[Remou]

1)How can I change the record selector in subform1 to the last record (when new record was selected) when I make a new record in subform2?

It would probably be best to use the recordset or the recordsetclone and the bookmark property for subform1.

2)Is there a code for Esc(aping) a new record? undo isn't working

I am not sure what you mean. You can undo a new record, but it depend on when and where you choose undo. In additon, the Before Insert event can be cancelled.

3)Why is de second solution working but gives an error on load or is there an better solution?

That would be to do with the order of loading for forms and subforms as far as I know. Are you talking about when there is a new record for the parent form? If so, it would be a good idea to put code on the parent form to ensure that data was entered in the correct order.

 

[KenReay]

Hi

If subform2 is subcontracts (Which I assume are children of (Contracts), should not subform2 be a subform of subform1 ?

Also have you got the linkfields between mainform and subform set up correctly?

Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
Website needs upgrading, but for now -

http://www.kenneth.reay.btinternet.co.uk

UK

 

[vdzr]

as i know, you can't link 2 subforms with eachother over child an parent methode. Only when subform2 is nested in the subform1 on the clientform and then i have the problem that i only see one contract with several subcontracts but not what I want : several contracts (with viewcheckbox for subcontracts) with several subcontracts for a client.

What will be the cancel before insert code?
docmd.CancelEvent? and will that undo a new record with a restricted field (not null)?

 

[vdzr]

I'll bild a new simple db with 1 clientform and 2 subforms (as a test)

Without 'bad' code, I have to click 2 times on [esc]. one time to empty the field and one time to exit the record from subform2

thanks already for the reply (i'm a newby)

 

[vdzr]

I found that a record undo possible is with

DoCmd.DoMenuItem acFormBar, acEditMenu, acUndo, , acMenuVer70

 

[Remou]

Have a look at RunCommand, DoMenuItem is deprecated by Microsoft.

 

[TheAceMan1]

How are ya vdzr . . .

It hasn't been established if your table structure is correct (considering how your linking the forms). For our reference, post back the following, proper for your current table structure:

[tt]Table:[blue]Clients[/blue]
*************
[blue]primClientID[/blue] as AutoNumber-[blue]PrimaryKey[/blue]-[purple]One Side[/purple] to Contracts.primClientID

Table:[blue]Contracts[/blue]
***************
[blue]ContractID[/blue] as AutoNumber-[blue]PrimaryKey[/blue]-[purple]One Side[/purple] to subContracts.ContractID
[blue]primClientID[/blue] as LongInteger-[blue]ForeignKey[/blue]-[purple]Many Side[/purple] to Clients.primClientID

Table:[blue]SubContracts[/blue]
******************
[blue]subContractID[/blue] as AutoNumber-[blue]PrimaryKey[/blue]
[blue]ContractID[/blue] as LongInteger-[blue]ForeignKey[/blue]-[purple]Many Side[/purple] to Contracts.ContractID[/tt]

If your table structure is correct ad all your primarykeys are autonumber, [blue]KenReay's[/blue] post should relieve you of having to manipilate any promarykeys at all!

[blue]Your Thoughts? . . .[/blue]

See Ya! . . . . . .

Be sure to see thread181-473997
Also faq181-2886

 

[vdzr]

My table connections are the same, only i don't use always autonumber because my clients have already a Personalnumber, for example X60A1 (a structured code on its own)

KenReay's post would certainly relieve me of having to manipilate any promarykeys at all(I've made that already with subform2subcontracts nested in details of subform1contracts) but ....
then I would only see one contract at a time (speed and easy handling is importand)

I've found a tirth solution : a button that will check the position of the selector in subform1 and that will start a new record in subform2

thanks by the way
and I like the colors in your reply