Form Opening Question

[SpiritedAway]

Hi - scratching around for a solution

I have a button on my MainForm - On click - if [textbox] on MainForm = "Live" then open [Form1] where DocID of MainForm is equal to DocID on [Form 1]

But if [textbox] on MainForm = "Postponed" then open [Form2] where DocID on MainForm is equal to DocID on [Form2]

But if [textbox]on MainForm = "Done" then open [Form3] where DocID on MainForm is equal to DocID on [Form3]

Can anyone advice the kind of code I would need to do this?

Using A2007

Thanks for your help

 

[boblarson]

You'll have to modify if names are different, etc.

Dim strForm As String
 
Select Case Me.TextBoxNameHere
    Case "Live"
        strForm = "Form1"
    Case "Postponed"
        strForm = "Form2"
    Case "Done"
        strForm = "Form3"
    Case Else
        Msgbox "Invalid input, acceptable values are 'Live', 'Postponed', or 'Done'", vbExclamation
        Exit Sub
End Select
   If strForm <> vbNullString Then
       DoCmd.OpenForm strForm, WhereCondition:="DocID = " & Me.DocID
   End If

If DocID is text then that line would need to be changed to:

DoCmd.OpenForm strForm, WhereCondition:="DocID = " & Chr(34) & Me.DocID & Chr(34)

 

[SpiritedAway]

the DocID is numeric..

I've also realised that the form i'm trying to open [FrmStatus] - has a subform [UserSub] and the DocID field is in that subform.

So from my MainForm under the click even on button - i used this code to test -

DoCmd.OpenForm "FrmStatus", , , "DocID = " & Me.UserSub.Form!DocID

but i'm getting a error message saying "UserSub is a Method or Data Member not found" - I've checked and rechecked UserSub - the spelling of this subform is correct...it does exist...

Any ideas why it is saying this - have I made it clear to the db that it's a subform on FrmStatus I'm trying to reference.

Once I can solve this problem I can include the code with the Select Case you mentioned.

Any insight would be appreciated.

 

[boblarson]

You don't refer to the form that you are trying to open. You refer to the form that IS opened. So the

"DocID = "

that part is referring to the field on FrmStatus. But if that field doesn't exist in the record source to frmStatus then that won't work.

The next part:

& Me.DocID

is the part that should be referring to the control name which is bound to DocID on the form that this button is on, not the form that is being opened.

If DocID is not in the frmStatus record source, then you will have to do it another way.

 

[SpiritedAway]

Hi Bob

I made an error - the code I pasted is wrong.

I got the code the wrong way round

DoCmd.OpenForm "FrmStatus", , , "DocID = " & Me.UserSub.Form!DocID - is not what I'm trying to do.


So that on my Mainform – when I hit the button it will open FrmStatus where both DocID on Mainform and FrmStatus matches – however the DocID in FrmStatus is in a subform UserSub.

Me.DocID is the Mainform
Me.FrmStatus.UserSub.DocID is the form I’m trying to open.

I’ve tried

DoCmd.OpenForm "FrmStatus", , , "UserSub.Form!DocID = " & Me.DocID


But the syntax is still giving me problems.

Once I solved that - i can then place it neatly into your Select Case you mentioned.

Could you advise how the correct syntax would look?

Apologies for being a pain.

 

[boblarson]

Again, if the item is on the subform you can't use that method.

Is the main form and the subform tied together with the master/child links? How do they relate?

 

[SpiritedAway]

FrmStatus (singleform) and UserSub (singleform) are child/master link StatusID

but the DocID from MainForm FrmUser (continuous form) and DocID in the UserSub pull their data from the same recordsource - tblUsers

The Mainform FrmStatus is based on a query of tblUser and tblStatus - it is locked and cannot be edited - to edit a record the user will do so in FrmStatus and depending on whether the project is live, postponed or finished will determine what fields need to be filled in. Hence the select statement.

 

[boblarson]

Okay, the code would be:

Dim rst As DAO.Recordset
 
DoCmd.OpenForm "FrmStatus"
 
Set rst = Forms!FrmStatus.UserSub.Form.RecordsetClone
 
rst.FindFirst "[DocID]=" & Me.DocID
 
If rst.NoMatch Then
   MsgBox "DocID not found.", vbInformation, "DocID: " & Me.DocID
Else
   Forms!FrmStatus.UserSub.Form.Bookmark = rst.Bookmark
End If
 
rst.Close
Set rst = Nothing

 

[SpiritedAway]

thanks bob - i'll give what you suggest a go.