Moving from one form to another

R

Roger Bell

In the Data Base design, i have two forms based on the same table. I have
placed a Command button on the Main Form to go to another Form. This works
Ok, except when the second form opens it always opens to record one, no
matter where I am in the Main form. I would, naturally like it to go to the
existing record in the Main Form. Maybe my design is up the creek!!
Any help would be appreciated
 
R

Rob Parker

Hi Roger,

Use the Where condition of OpenForm command to tell the second form which
record to display, as in this example:

DoCmd.OpenForm "YourFormName",,,"RecordID = " & Me.RecordID

Change the form name and key field to suit.

If the key field you want to use in the Where condition is a text field, you
will need quote marks as delimiters:
DoCmd.OpenForm "YourFormName",,,"RecordID = '" & Me.RecordID & "'"
If the key field is a datetime field, you will need # delimiters"
DoCmd.OpenForm "YourFormName",,,"RecordID = #" & Me.RecordID & "#"

HTH,

Rob
 
R

Roger Bell

Thanks for that Rob. Got it to work thanks to your expertise. However, when
you move to the other form it filters just the record you are on. The user
needs to be able to acccess other records whilst in this screen. Is there a
way to do this?
Thanks again for your valued support
 
A

Albert D. Kallal

Roger Bell said:
Thanks for that Rob. Got it to work thanks to your expertise. However,
when
you move to the other form it filters just the record you are on. The
user
needs to be able to acccess other records whilst in this screen. Is there
a
way to do this?
Thanks again for your valued support
Click to expand...

Yes, you can accomplish you goal. As a side note, the movign to ONE reocrd
is perffered from a performance point fo view. Designs that limit, or only
load one reocrd to a form tend to be very high perframance appcations.

however, do accoplish your goal, the soltion then is to remote the "where"
clause, and simply *move* to the same reocrod.

Here is how the code will look:

' the first thing to remmber is that if you ALLOW editign of data in the
first form, but then open a 2nd form to the SAME reocord, you MUST FORCE A
DISK WIRITE in the first form (else, you ahve two forms open to the saem
reocrd..and BOTH will want to save their changes!!!).

so, the follwing code will force a disk write in our first form. You can
IGRNORE/remove the folwlign code if you don't allow editign in the first
form

dim strF as string
' write current reocrd to disk before lanching 2nd form
if me.Dirty = true then
me.dirty = false
end if

strF = "YourFormName"

' open 2nd form
DoCmd.OpenForm strF
' move 2nd form to same reocrd

forms(strF).reocrdSet.findFirst "id = " & me!id
 
R

Roger Bell

Thanks for that Albert. Would you be able to tell me the exact code in
relation to the Event Procedure as follows:

Private Sub PLANNED_GIVING_PROGRAMME_Click()
stDocName = "Planned Giving Programme"
If InputBox("Please Enter Password") = "roger" Then
DoCmd.OpenForm stDocName, , , "[Envelope Number] = " & Me.[Envelope
Number]
Else
MsgBox "Sorry, Incorrect Password"
End If
On Error GoTo Err_PLANNED_GIVING_PROGRAMME_Click



Exit_PLANNED_GIVING_PROGRAMME_Click:
Exit Sub

Err_PLANNED_GIVING_PROGRAMME_Click:
MsgBox Err.Description
Resume Exit_PLANNED_GIVING_PROGRAMME_Click

End Sub
 
R

Rob Parker

Hi Roger,

Try this:

Private Sub PLANNED_GIVING_PROGRAMME_Click()
Dim stDocName As String
Dim stFindRecord As String

On Error GoTo Err_PLANNED_GIVING_PROGRAMME_Click

stDocName = "Planned Giving Programme"
If InputBox("Please Enter Password") = "roger" Then
If Me.Dirty Then Me.Dirty = False 'force save of any pending edits
DoCmd.OpenForm stDocName
Forms(stDocName).RecordSet.FindFirst "[Envelope Number] = " &
Me.[Envelope Number]
Else
MsgBox "Sorry, Incorrect Password"
End If

Exit_PLANNED_GIVING_PROGRAMME_Click:
Exit Sub

Err_PLANNED_GIVING_PROGRAMME_Click:
MsgBox Err.Description
Resume Exit_PLANNED_GIVING_PROGRAMME_Click

End Sub

Note also that I've moved your On Error GoTo statement to the start of the
routine, so that it will be triggered if an error occurs during the routine.

It also occurs to me to wonder why you have two forms with the same
recordsource. Why not simply put controls for all the fields you want on
the first form? If there are a lot of them (possibly a sign of
non-normalised data structure, but not necessarily), you could use a tabbed
interface on your form to group the controls into logical sets; however,
they would still all be on the same form, so the issue of opening another
form and navigating to the same record would be avoided.

Rob

Roger Bell said:
Thanks for that Albert. Would you be able to tell me the exact code in
relation to the Event Procedure as follows:

Private Sub PLANNED_GIVING_PROGRAMME_Click()
stDocName = "Planned Giving Programme"
If InputBox("Please Enter Password") = "roger" Then
DoCmd.OpenForm stDocName, , , "[Envelope Number] = " & Me.[Envelope
Number]
Else
MsgBox "Sorry, Incorrect Password"
End If
On Error GoTo Err_PLANNED_GIVING_PROGRAMME_Click



Exit_PLANNED_GIVING_PROGRAMME_Click:
Exit Sub

Err_PLANNED_GIVING_PROGRAMME_Click:
MsgBox Err.Description
Resume Exit_PLANNED_GIVING_PROGRAMME_Click

End Sub


Albert D. Kallal said:
Yes, you can accomplish you goal. As a side note, the movign to ONE
reocrd
is perffered from a performance point fo view. Designs that limit, or
only
load one reocrd to a form tend to be very high perframance appcations.

however, do accoplish your goal, the soltion then is to remote the
"where"
clause, and simply *move* to the same reocrod.

Here is how the code will look:

' the first thing to remmber is that if you ALLOW editign of data in the
first form, but then open a 2nd form to the SAME reocord, you MUST FORCE
A
DISK WIRITE in the first form (else, you ahve two forms open to the saem
reocrd..and BOTH will want to save their changes!!!).

so, the follwing code will force a disk write in our first form. You can
IGRNORE/remove the folwlign code if you don't allow editign in the first
form

dim strF as string
' write current reocrd to disk before lanching 2nd form
if me.Dirty = true then
me.dirty = false
end if

strF = "YourFormName"

' open 2nd form
DoCmd.OpenForm strF
' move 2nd form to same reocrd

forms(strF).reocrdSet.findFirst "id = " & me!id
Click to expand...
Click to expand...
 
R

Roger Bell

Ok Thanks Rob. Will try the procedure and let you know the result. I
actually do have tab controls on the form, but when the user clicks to the
Planned Giving Tab, which is Password protected, the user can still view the
information on this screen which is not acceptable. Have tried using a
Masked Rectangular Box to cover this Page with no real success.
Thanks again fro your valued support

Rob Parker said:
Hi Roger,

Try this:

Private Sub PLANNED_GIVING_PROGRAMME_Click()
Dim stDocName As String
Dim stFindRecord As String

On Error GoTo Err_PLANNED_GIVING_PROGRAMME_Click

stDocName = "Planned Giving Programme"
If InputBox("Please Enter Password") = "roger" Then
If Me.Dirty Then Me.Dirty = False 'force save of any pending edits
DoCmd.OpenForm stDocName
Forms(stDocName).RecordSet.FindFirst "[Envelope Number] = " &
Me.[Envelope Number]
Else
MsgBox "Sorry, Incorrect Password"
End If

Exit_PLANNED_GIVING_PROGRAMME_Click:
Exit Sub

Err_PLANNED_GIVING_PROGRAMME_Click:
MsgBox Err.Description
Resume Exit_PLANNED_GIVING_PROGRAMME_Click

End Sub

Note also that I've moved your On Error GoTo statement to the start of the
routine, so that it will be triggered if an error occurs during the routine.

It also occurs to me to wonder why you have two forms with the same
recordsource. Why not simply put controls for all the fields you want on
the first form? If there are a lot of them (possibly a sign of
non-normalised data structure, but not necessarily), you could use a tabbed
interface on your form to group the controls into logical sets; however,
they would still all be on the same form, so the issue of opening another
form and navigating to the same record would be avoided.

Rob

Roger Bell said:
Thanks for that Albert. Would you be able to tell me the exact code in
relation to the Event Procedure as follows:

Private Sub PLANNED_GIVING_PROGRAMME_Click()
stDocName = "Planned Giving Programme"
If InputBox("Please Enter Password") = "roger" Then
DoCmd.OpenForm stDocName, , , "[Envelope Number] = " & Me.[Envelope
Number]
Else
MsgBox "Sorry, Incorrect Password"
End If
On Error GoTo Err_PLANNED_GIVING_PROGRAMME_Click



Exit_PLANNED_GIVING_PROGRAMME_Click:
Exit Sub

Err_PLANNED_GIVING_PROGRAMME_Click:
MsgBox Err.Description
Resume Exit_PLANNED_GIVING_PROGRAMME_Click

End Sub


Albert D. Kallal said:
Thanks for that Rob. Got it to work thanks to your expertise.
However,
when
you move to the other form it filters just the record you are on. The
user
needs to be able to acccess other records whilst in this screen. Is
there
a
way to do this?
Thanks again for your valued support

Yes, you can accomplish you goal. As a side note, the movign to ONE
reocrd
is perffered from a performance point fo view. Designs that limit, or
only
load one reocrd to a form tend to be very high perframance appcations.

however, do accoplish your goal, the soltion then is to remote the
"where"
clause, and simply *move* to the same reocrod.

Here is how the code will look:

' the first thing to remmber is that if you ALLOW editign of data in the
first form, but then open a 2nd form to the SAME reocord, you MUST FORCE
A
DISK WIRITE in the first form (else, you ahve two forms open to the saem
reocrd..and BOTH will want to save their changes!!!).

so, the follwing code will force a disk write in our first form. You can
IGRNORE/remove the folwlign code if you don't allow editign in the first
form

dim strF as string
' write current reocrd to disk before lanching 2nd form
if me.Dirty = true then
me.dirty = false
end if

strF = "YourFormName"

' open 2nd form
DoCmd.OpenForm strF
' move 2nd form to same reocrd

forms(strF).reocrdSet.findFirst "id = " & me!id
Click to expand...
Click to expand...
Click to expand...
 
Top