Passing Variable to another form (Access2007)

[David Eagar]

I have a form which contains a subform (continuous data). In the subform the is a command button that opens a new form (to a specific record). All functions normally except when I try to add a new record on the second form.

The macro created on the command button is
2frmSightDetails, Form, , ="[ssl]=" & [SightID], , Normal

but when I enter new data, all goes in except ssl (fk), which is left empty. How come if it knows that [ssl]=[SightID] why is [ssl] empty?

What is the correct procedure to handle this situation?

Thanks in advance

 

[gemma-the-husky]

opening a form to a specific record isnt the same thing as saying that a new record will have the same key

that behaviour only works for a form/subform

in this case, you need to explicit set the value you need in the forms "before update event" (maybe just for a newrecord also)

 

[David Eagar]

in this case, you need to explicit set the value you need in the forms "before update event" (maybe just for a newrecord also)


???? once again in plain English (my VBA is very poor)

 

[gemma-the-husky]

when you save the record, you need to explicitly assign the value - since it wont happen automatically

something like

[SightID] = [ssl]

sightid needs to be a field name, andf SSL a variable in your program, i guess

--------
the place to do this is in the before update event, and as you only need to do this in a newrecord then
this sort of thing - hence the questions about sightid and ssl

form beforeupdate event
if me.newrecord
[SightID] = [ssl]
end if

 

[David Eagar]

Thnks for that - tried
if me.newrecord
[SightID] = [ssl]
end if

but if me.newrecord was objected to by VBA

then tried [SightID] = [ssl] and was told it could not find the object I was referring to

 

[gemma-the-husky]

sorry

if me.newrecord THEN
[SightID] = [ssl]
end if

i said you needed to look at how you get at the other ifelds

sightid needs to be a textbox on your form
ssl needs to be a variable (or obtained by a reference to a text box on the other form)

 

[David Eagar]

Still no go. [SightID] comes from a subform on form1, [ssl] is on form2 [SightID] is also on form2

tried latest

if me.newrecord THEN
[ssl] = [SightID]
end if


but no value was entered for [ssl] so couldn't save record

 

[John Big Booty]

Check this link for the correct syntax for referring to Subforms and their controls.

 

[wiklendt]

also you may want to look into the openArgs argument of the DoCmd.OpenForm command.

 

[wiklendt]

sorry - i just realised you're using macros. i don't know if openargs work within macros?

 

[David Eagar]

Damn, I still can't get this thing going. Have attached sample db - Object is to be able to add a new record on Form3 after clicking on the magnifying glass on Form1

 

[Avelino]

Use the property "default value" field ssl (Form3) to pass the value of SightID (form2sub)

example

= [Forms]! [Form1]! [Form2sub]. [Form]! [SightID]

download the file with the modification

 

[David Eagar]

Brilliant!! - It's so obvious once someone points it out

Very many thanks