Open form using a tempvars

[Graham1894]

Hi, I hope someone can help because I can’t seem to make this work.

When a user needs to update a record but do not have the authorisation to make the changes my DB sends a task to their line manager for them to complete the task.

Currently, the line manager opens the task form which gives them the information of what needs to be done, they then close the task and go to the record to make the changes.
I’ve been ask if I can put a button on the task form that would take them to the record. The problem is it works across all the tables.

In the task table I put three columns, to pass what I thought would be all the information I would need, (txtForm, txtCode, recID) these are then hidden controls on the task form so the information is in the form.
TxtForm is the form that needs to open, i.e frmCustomerDetails or frmQuoteDetails
txtCode is the name of the primary key i.e CustomerID or QuoteID
recID is the ID number i.e 5 or 8

VBA Code used
TempVars!txtForm = Me.txtForm.value
TempVars!txtCode = Me.txtCode.value
TempVars!txtRecID = Me.recID.value
Call CloseAllForms
DoCmd.OpenForm TempVars!txtForm, acNormal, , " TempVars!txtCode = " & TempVars!rstRecID
TempVars.RemoveAll

I can’t get the where clause to work, any help would be appreciated.
Thanks

 

[plog]

Variable=", and this is inside a variable, instead of the variable this text will be put in its place"

"This is a literal, whatever is been the quotes is what literally appears" & Variable

Which resolves to this:

"This is a literal, whatever is been the quotes is what literally appears, and this is inside a variable, instead of the variable this text will be put in its place"


What you did is this:

"This is a literal " & "Variable"

Which resolves to this:

"This is a literal Variable"

When you put quote marks around things they literally appear with whats between those quotes---you are not using them as variables, you are simply using the variable name as text.

 

[Graham1894]

Hi plog,

I know " TempVars!txtCode = " & TempVars!rstRecID is wrong but I just can't work out how to make it work.

Could you help?

 

[plog]

Your where condition is ultimately a string. You are not compiling that string correctly. Find out what that string actually is so you can view it with your own eyes.


strWhere= [put your where string logic here]
Debug.Print strWhere

 

[MajP]

I do not understand the need for any TempVars. If the values for the form name, code, and recID are stored in the Task Table, why do you need to turn these into tempvars? Why can you not just use them directly?

 

[MajP]

DoCmd.OpenForm me.txtForm, acNormal, , me.txtCodeID & " = " & me.RecID

 

[Graham1894]

Hi plog

I've never had to use a variable for a control name before and all logic seemed to be going out the window.


Thank you for pointing me in the right direction, I just had to change the where clause to;

TempVars!rstCode & " = " & TempVars!rstRecID

It now opens the right form at the right record regardless of the name of the primary key.

 

[Graham1894]

Majp

Sorry I hadn't seen your post, I was busy working it out from Polg's post, and I came up with the same code as you.

The reason I'm using tempvars is because I'm closing all open forms first and then opening the form that needs editing.

 

[MajP]

The reason I'm using tempvars is because I'm closing all open forms first and then opening the form that needs editing

Still, zero need for any tempvars. Just use a regular variable. Or simply open your form and close all other forms.