Link to home
Create AccountLog in
Avatar of Johnni I
Johnni I

asked on

Open New form record not parsing value

Hi EE People

I have 2 forms where the current record on Main Form has a button that will open a corresponding record on another form.  The code I have found allows me to use a button to navigate to the record required and if there is no record, will open a new form to populate.  This is working well except when the new form is created,  the field "RoomID" from the first form is not being inserted into the new form's field "Room".  They are linked in a one to one relationship.  The is no code in the on load event of the second form.

Main form: frmRooms and field RoomID
Second Form: frmAV_EDIT and Foreign Key "Room"
Private Sub cmdt3_Click()

If Not Me.NewRecord Then
    DoCmd.OpenForm "frmAV_EDIT", _
        WhereCondition:="Room=" & Me.RoomID
End If

End Sub

Open in new window

Avatar of Jim Dettman (EE MVE)
Jim Dettman (EE MVE)
Flag of United States of America image

<<The code I have found allows me to use a button to navigate to the record required and if there is no record, will open a new form to populate.>>

 Where is the new form code?   This code that you posted opens frmAV_EDIT on an existing record.

Or are you saying when you open this form and there is no corresponding record?

If so, I would pass the RoomID as part of the OpenArgs as well:

If Not Me.NewRecord Then
    DoCmd.OpenForm "frmAV_EDIT", _
        WhereCondition:="Room=" & Me.RoomID
        OpenArgs:=Me.RoomID
End If

 and in the BeforeInsert event:

 Me.<some control or the field> = Me.OpenArgs

Jim.
try this

Private Sub cmdt3_Click()

If Not Me.NewRecord Then
    DoCmd.OpenForm "frmAV_EDIT", _
        WhereCondition:="Room=" & Me.RoomID
		
	else
	    DoCmd.OpenForm "frmAV_EDIT", _
         Datamode:=acFormAdd, OpenArgs:=Me.RoomID
End If

End Sub

Open in new window


in the open or load event of the form "frmAV_EDIT" place this code

private sub form_open(cancel as integer)
if me.newrecord and me.openargs & ""<>"" then
   me.txtRoom=me.openargs
end if
end sub

Open in new window

ASKER CERTIFIED SOLUTION
Avatar of Gustav Brock
Gustav Brock
Flag of Denmark image

Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
I would use Jim's suggestion which doesn't apply the RoomID until the user actually starts typing in the new record.  The other suggestions dirty the record with code and that can confuse the user if he ends up not actually saving the record or you can end up creating "empty" records if you don't have required fields defined.

Populating the RoomID in the BeforeInsert event means that only the user dirties the record.  Therefore, he knows he did something and so if he attempts to abandon the record before saving, he will understand why he is getting error messages.  If you dirty the record behind the scenes, the user will not understand why he is being nagged if he attempts to cancel the save.

The BeforeInsert event runs immediatly after the first character is typed into the form in ANY control so it is the appropriate event to use for code that will add additional data to the form.
Avatar of Johnni I
Johnni I

ASKER

Gustav's solution works perfectly.  I am able to open existing forms and where I have a new RoomID and no form (frmAV_EDIT) associated, a new form opens and when I enter data (a series of check boxes) the RoomID is inserted into the Room field.  Cool!

Jim's solution game  me a compile errors and incorrect syntax message on the line "OpenArgs:=Me.RoomID"

I was bleary eyed when I found the code and now realise that I had inadvertantly changed the code from the original and somehow ended up getting a new record if no record exists in the frmAV_EDIT.  I didn't need to insert any additional code in the frmAV_EDIT.

There are valid questions raised regarding clearing and cancelling the form which I will have to test for,  but every RoomID will have a corresponding form in frmAV_EDIT