Opening Form from previous ID

[Elmobram22]

Hi,

I am using Access 2010 and want to open a new form based on an ID from a previous form. Access makes this very easy to do in the macro builder but I am unable to replicate this in VBA afterwards. The reason behind this is I want it to also run some code to export some info to Outlook. I am only filtering the ID from one form to the next. The ID is from a single table.

Cheers,

Paul

 

[CJ_London]

Access provides an option to convert your macro to vba code if you want, so you can see what it looks like

but from your description, in vba I would expect you to use

docmd.openform "formName",,,"ID=" & me.ID

 

[Elmobram22]

Hi,

Thanks for the quick reply. The option to convert is greyed out unfortunately. I have tried your code but it comes up with an error.

My macro looks like this...

CloseWindow
Object Type - Form
Object Name - Frm2
Save - Yes

OpenForm
Form Name - Frm3
View - Form
Filter Name -
Where Condition - =="[StaffID]=" & [StaffID]
Data Mode -
Window Mode - Normal

My VBA is...

Private Sub Command18_Click()
DoCmd.Close
DoCmd.OpenForm "FrmMot", , , "StaffID=" & Me.StaffID
End Sub

 

[CJ_London]

without knowing what error you are getting, I cannot help further.

The only thing I note is your macro is opening frm3 and your code is opening frmMOT

 

[Elmobram22]

Run-time error 2467

The expression you entered refers to an object that is closed or doesn't exist.

Yes the reason it refers to a different form is that is the working macro from another form that does the same thing. I want the ability to do that in VBA if possible. Thanks again.

 

[CJ_London]

google "The expression you entered refers to an object that is closed or doesn't exist."

You will find multiple links to the subject with many different reasons for the error occurring.

But basically it is saying that either

FrmMot does not exist or has the same name as a different object (e.g.table or query)
and/or StaffID does not exist in FrmMOT
and/or Me.StaffID is not in the form you are calling or is null

 

[Elmobram22]

Managed to convert the macro to VBA and got this. Don't know if it is helpful?


'------------------------------------------------------------
' Macro1
'
'------------------------------------------------------------
Function Macro1()
On Error GoTo Macro1_Err

With CodeContextObject
DoCmd.Close acForm, "FrmFinalRota"
DoCmd.OpenForm "Final Step", acNormal, "", "[StaffID]=" & .StaffID, , acNormal
End With


Macro1_Exit:
Exit Function

Macro1_Err:
MsgBox Error$
Resume Macro1_Exit

End Function

 

[CJ_London]

not really, the error is the same and the answer is the same - modified for the revised information

But basically it is saying that either

form 'Final Step' does not exist or has the same name as a different object (e.g.table or query)
and/or StaffID does not exist in 'Final Step'
and/or Me.StaffID is not in the form you are calling from or is null

 

[Elmobram22]

Ok thanks for your replies hopefully I can get it sorted and when I have I'll post back

 

[CJ_London]

the other possibility is the error in being generated in form 'Final Step' opening events, one of its controls or one of its subforms if they exist, preventing it from opening

The opening events are
open
load
activate
resize
current

 

[Elmobram22]

It's just weird cause if I use embedded macro option it works fine

 

[CJ_London]

just noticed this

DoCmd.OpenForm "Final Step", acNormal, "", "[StaffID]=" & .StaffID, , acNormal

Normally this is left blank so with the double quotes, Access may be trying to find an object called ""

However you didn't populate it in your earlier examples so perhaps not an issue

 

[Elmobram22]

I managed to figure it out a different way. Rather than closing the form first you open the new form and then change focus to the previous form and close it. Therefore it holds the value rather than trying to hold the information after you have closed the form.

Private Sub ButtonName_Click()
DoCmd.OpenForm "FormYouWantToOpen", , , "ID = " & Me!ID
Forms("FormYouWantToClose").SetFocus
DoCmd.Close
End Sub