Checkbox problems

[Deverry]

I have a form with a checkbox field. When the checkbox = true I want a popup box that asks for additional information (data to go to the same underlying table or as a sub-table).

I am able to get the popup box to activate via code in the After Update
If Me.SP = True Then
DoCmd.OpenForm "frmSponsorshipDetails", acnormal, "", "", , acNormalWindow
End If
End Sub

Two problems seem to occur with this.

1. Records are not synchronised ie subform always defaults to record 1
2. Once I've closed the sub-form and try to create a new record I get an
error msg stating that someone else has made changes to record.

I have identical ID fields in both forms - how do I get the forms to link and work together?

Or must I use a sub-form? If so is there any way to make the sub-form a pop-up box?

As ever, am slow to grasp the complexities of coding *sigh*

Lukim

Dev

 

[Exodus]

Use a seperate table for your pop up form. After all there is not always going to be data for these fields right. So if it's all in one table you have a bunch of empty fields.

Create a new table and form with the fields you want

Then in the after update event for the check box put

If Me.SP = True Then
DoCmd.OpenForm "YourForm", acNormal, , , , acNormal, Me.YourMainForm!YourIDField
End IF
End Sub

Now on the on Open Event of the Pop up Form put

Me.YourIdField = Me.OpenArgs

What this does is use opening argument to tell the pop up form the correct Id to use.

 

[Deverry]

Thanks Exodus for your help.

I was already working on the separate table theory but had made it a sub-table. I've now removed the relationship and inserted the code you gave me but am still receiving problems *argh*

This is what I wrote

Private Sub SP_AfterUpdate()
If Me.SP = True Then
DoCmd.OpenForm "frmSponsorshipDetails", acNormal, , , , acNormal, Me.frmSponsors!ID
End If

End Sub

When I check the check box I get a Compile error - method or data member not found and it highlights .frmSponsors!ID

I checked and double checked I have the form name correct for my main form so what AM I doing wrong?

For the popup box form I have placed in the On Open Event

Private Sub Form_Open(Cancel As Integer)
Me.ID = Me.OpenArgs
End Sub

Again thanks for your help it is much appreciated.

Dev

 

[Exodus]

Try Using

Me!frmSponsors!ID

what version of access do you have.

 

[Deverry]

Thanks but that doesn't work either I'm afraid. For now I'm giving up as I've wasted too much time on it and have gone back to using a single form and table.
My version of Access is XP (2002 I think).

Lukim
Dev

 

[WayneRyan]

Dev,

It is best to keep the separate tables. You have a few ways to
do this.

1) Make the other form a subform. The easiest thing to do is go
to the subform's properties. Then Select the "Master-Child" relationships.
then Access will "synch" your two tables ... no code.

You can make the subform ".Invisible" based on the checkbox if you want,
but why hide data?

2) Base a form on a query, for the Key Field, you can reference the
Criteria like: Forms![YourMainForm]![YourKey]. Then whenever you open it
the subform will be in synch.

3) Base a form on a table, and use the .OpenForm method:

DoCmd.OpenForm "frmSponsorshipDetails", acNormal, , , , acNormal, "[ID] = " & Me.ID

You have to tell it that you're restricting it by using the [ID] field.

Wayne

 

[Exodus]

Not to disregard waynes advice, I gave something wron and have to fix it, but the problem with the code I gave is
That I was referencing a subform in the open args just use
this

Me.ID