How to Set Temporary Value

[tihmir]

Hi, I need to change my Macro to VBA code, but I'm wrong somewhere. So I have this Macro on my command button "cmd_1":

UG Added Code Tags

SetTempVar
     Name: temVarLaboratory
     Expression: [LaboratoryID]
OpenForm
     FormName: sub_Indicators
     Where condition = [LaboratoryID]=[TempVars]![temVarLaboratory]
 
'That is the Macro code who I use and it works well, but I need to write in VBA code, 
'because I want to add a few more things:
    If Me.txt_HM= 0 Then
        MsgBox "Please tick checkbox"
        Me.txt_HM.SetFocus
        Cancel = True
        Exit Sub
    End If

 

[MajP]

No need to waste your time on tempvars. Just use a where condition
https://docs.microsoft.com/en-us/office/vba/api/access.docmd.openform

 

[Gasman]

I tend to use the format

TrempVars("temVarLaboratory") = [LaboratoryID]

but you do noy need a Tempvar here and could justuse the [LaboratoryID] directly

DoCmd.OpenForm,,,"[LaboratoryID]=" & Me.LaboratoryID

use whatever you were using to set the tempvar if it is within scope.

HTH

 

[tihmir]

but you do noy need a Tempvar here and could justuse the [LaboratoryID] directly

Code:
DoCmd.OpenForm,,,"[LaboratoryID]=" & Me.LaboratoryID

I have tried with DoCmd.OpenForm "sub_Indicators", , , "[LaboratoryID] = " & Me.LaboratoryID, but when the form is open LaboratoryID is set to "0".

I wonder why in the loaded form "sub_Indicators" Property sheet - Filter [LaboratoryID]= 652 (this is the correct number in first form), but on form view the field LaboratoryID is "0" ?

 

[Gasman]

Well assigning it to another variable is not going to fix that problem.
Walk through the code in the debug window with F8 after setting a breakpoint.

Sounds like you are not referencing the correct control?

HTH

 

[Pat Hartman]

Is the form set to open as Add?

 

[tihmir]

Is the form set to open as Add?

No, it is not. Have I set to open as Add?

 

[Pat Hartman]

No, if it were defined to open in Add mode, it would ignore the where argument so it would open to a new record rather than an existing record.

 

[tihmir]

I can't fix the problem the code:

DoCmd.OpenForm "sub_Indicators", , , "[LaboratoryID] = " & Me.LaboratoryID

In Immediate windows show me this error: Compile error: Variable not yet created in this context.
Аny ideas for the reason?

 

[Gasman]

LaboratoryID is not a control on your form?

 

[tihmir]

No, it is not a control. LaboratoryID on the second form is set by

Form_Load()

DoCmd.GoToRecord , , acNewRec.

 

[Gasman]

I'm lost on what is first form and what is second form

You said eearlier that syntax was giving you zero when opening the form. You told Pat that you were not opening the form in Add mode, yet now you say the load has DoCmd.GoToRecord , , acNewRec, which as far as i know is doing the same thing, just a different method.?

Best upload your db or cut down version and explain the steps needed to replicate your problem, else we could be back and forth for days.
If it cannot be opened in 2007, then other will need to assist.

 

[tihmir]

I'm lost on what is first form and what is second form

You said eearlier that syntax was giving you zero when opening the form. You told Pat that you were not opening the form in Add mode, yet now you say the load has DoCmd.GoToRecord , , acNewRec, which as far as i know is doing the same thing, just a different method.?

Best upload your db or cut down version and explain the steps needed to replicate your problem, else we could be back and forth for days.
If it cannot be opened in 2007, then other will need to assist

It turned out to be quite confusing with all these explanations. I'll upload db

 

[Gasman]

Best remove the Solved marker as well, as it has not been yet and might get more attention then.?

 

[tihmir]

Here is my database. I put two buttons on the form"subformNonStandard" cmdHm and cmdMB. When I click on the cmdMB I have Macro to open form "Form_mb" to specific record , and it opens the form "Form_mb" correct.
When I try to open the form "Form_hm" by click on the button cmdHm with this code

DoCmd.OpenForm "Form_hm", , , "[LControlsID]=" & Me.LControlsID

it opens the form, but not to specific recort.
What I miss in my code?

 

[Gasman]

I get unrecognised database format, so I cannot help with that, sorry.

 

[tihmir]

I get unrecognised database format, so I cannot help with that, sorry.

Would you try now? I replaced it

 

[Gasman]

Nope, still get the error. I have a very old version of Access.

 

[tihmir]

Nope, still get the error. I have a very old version of Access.

The db is created on Access 2019.
OK, not a problem.

Thanks anyway for your time. Maybe someone else will help with my problem.

 

[Gasman]

The db is created on Access 2019.
OK, not a problem.

Thanks anyway for your time. Maybe someone else will help with my problem.

Can you not save in a different format?

https://support.office.com/en-gb/ar...e-format-59369336-6d58-4094-b396-ee5bb637717b