Passing variable between events within same form (1 Viewer)

[Lissa]

Hi Everybody,
I've been searching the forum for information on passing variables but I'm not sure how I would apply it in my case. Can someone help?

I have a subform of a purchase order form. As the user enters the first line item, I have to check what type of line item the entry is. In the Before_Update event of that subform, I ask the user a Y/N question about the line item. I have two cmd buttons that the user would click to indicate their answer (Y/N). I would like the Y/N answer variable to be known to the code in the After_Insert event of the same form.

Do I declare the answer variable in a Module as a public variable?

Thanks in advance!

 

[Steve R.]

You can declare it as a "public" variable in the form's declaration section. Alternatively, you can have a hidden textbox for temporarily holding values.

 

[Lissa]

Thanks I'm trying that right now but it seems as though the Before_Update and After_Insert are firing off at the same time or the After_Insert code is firing before the user has a chance to answer the question.....

 

[LPurvis]

Are these both events of the same form?
There's no need for the variable to be declared public then - just module level (declared in the module declarations section).
e.g. standard
Dim varVariable As Variant 'Or whatever
or equally
Private varVariable As Variant

BeforeUpdate does indeed get raised before AfterInsert.
What's indicating to you that this isn't happening?

 

[Lissa]

The Before_Update event opens a form that asks the user the Y/N question. I want to read in that Y/N response into the After_Insert event of the originating form. In response to your question, the Before and After events are on the same form.
By opening the other form in dialog mode, I can at least see that there is a pause between the user question and the firing of the After_Insert code but the After Insert code still does not know the variable's value. I guess I'm not declaring it in the right place? I added this to both the subform that calls the prompting form...

Option Compare Database
'Is the PO line item a consumable? Y/N
Public consumable As String

 

[LPurvis]

Hi

You mention
"both the subform that calls the prompting form."
But that is for completely distinct use yeah - each subform has its own BeforeUpdate and AfterInsert events and operate internally?

The declarations section should look like

Option Compare Database
Option Explicit
'Is the PO line item a consumable? Y/N
Dim consumable As String

The Option Explicit is important to make sure that the variables being referred to are consistent.
AFAICR "consumable" isn't a reserved word - though a type prefix usually helps make that unlikely - e.g. Dim strConsumable As String.

The called form is called in dialog mode you say (which is correct).
But if the order of events is preserved then it does come down to identifying the variables. Try the above declaration section example to start with and make sure that all is as it should be.

 

[Lissa]

First, thank you trying to help.
Second... no luck. Here is what I have so far..

Events from the subform "sbfPurchaseOrders" that contains the Before and After events...
Option Compare Database
Option Explicit
'Is the PO line item a consumable? Y/N
Dim strConsumable As String

Private Sub Form_AfterInsert()
Dim strSQL As String
Dim counter As Integer
Dim currentDt As Date
counter = 0
currentDt = Now

If strConsumable = yes Then
MsgBox "This is the value " & strConsumable & ""
strSQL = "INSERT INTO tblPART_STATUS (SerialNumber,OrderDate, DueDate, StatusDate, StatusId, PartInvId, PO_Number) VALUES ('TBD', # " & Forms![frmPurchaseOrder]![OrderDate] & " #, # " & DueDate & " #, #" & currentDt & "#, '1', " & PartInvId & ", '" & Forms![frmPurchaseOrder]![PONumber] & "')"
CurrentDb.Execute strSQL, dbFailOnError
MsgBox "I got here..."
Else
Do Until counter = Me.Quantity
strSQL = "INSERT INTO tblPART_STATUS (SerialNumber,OrderDate, DueDate, StatusDate, StatusId, PartInvId, PO_Number) VALUES ('TBD', # " & Forms![frmPurchaseOrder]![OrderDate] & " #, # " & DueDate & " #, #" & currentDt & "#, '1', " & PartInvId & ", '" & Forms![frmPurchaseOrder]![PONumber] & "')"
CurrentDb.Execute strSQL, dbFailOnError
counter = counter + 1
Loop
End If
End Sub

Private Sub Form_BeforeUpdate(Cancel As Integer)
Dim stDocName As String
Dim stLinkCriteria As String
stDocName = "frmConsumablePrompt"
DoCmd.OpenForm stDocName, acNormal, , , , acDialog

End Sub

And here is the code from the form that prompts the user (frmConsumablePrompt) that is called by the Before_Update event of sbfPurchaseOrders
Option Compare Database
Option Explicit
'Is the PO line item a consumable? Y/N
Dim strConsumable As String

Private Sub cmdYES_Click()
On Error GoTo Err_cmdYES_Click
strConsumable = yes
DoCmd.Close
Exit_cmdYES_Click:

Exit Sub
Err_cmdYES_Click:
MsgBox Err.Description
Resume Exit_cmdYES_Click

End Sub
Private Sub cmdNO_Click()
On Error GoTo Err_cmdNO_Click

strConsumable = no
DoCmd.Close
Exit_cmdNO_Click:
Exit Sub
Err_cmdNO_Click:
MsgBox Err.Description
Resume Exit_cmdNO_Click

End Sub
________________________________

I wasn't sure if the 'Option Explicit' form belonged in both forms or if having it in the form where the variable assignments occur is sufficient enough. But it errors if the user clicks the No cmd.

 

[LPurvis]

Alrighty then. Important to see that.
The two forms are using two distinct instances of a variable named strConsumable.
While you could make one of the instances PUblic and refer to it from the other form - that would serve to tie that form into use with only the calling form specified (as it has to refer to the form as part of the expression of the pubulic variable
e.g.
Forms!FormName!SubformCtlName.Form.strConsumable
which is very sepcific.

More common is to pass the value to the called form - and request it back by not actually closing the dialog (a dialog mode form is also dismissed by setting it invisible - allowing the calling code to continue while still being able to refer to the, now hidden, dialog form).

e.g. passing the data
DoCmd.OpenForm stDocName, WindowMode:= acDialog, OpenArgs:= strConsumable
(which isn't very important in your scenario - strConsumable will be unassigned at this point).

You dialog code would become:

Option Compare Database
Option Explicit
Public strConsumable As String
 
Private Sub Form_Open(Cancel As Integer)
    strConsumable = Me.OpenArgs
End Sub
 
Private Sub cmdYES_Click()
On Error GoTo Err_cmdYES_Click
 
    strConsumable = yes
    Me.Visible = False
 
Exit_cmdYES_Click:
    Exit Sub
Err_cmdYES_Click:
    MsgBox Err.Description
    Resume Exit_cmdYES_Click
End Sub
 
Private Sub cmdNO_Click()
On Error GoTo Err_cmdNO_Click
 
    strConsumable = no
    Me.Visible = False
 
Exit_cmdNO_Click:
    Exit Sub
Err_cmdNO_Click:
    MsgBox Err.Description
    Resume Exit_cmdNO_Click
End Sub

And your calling code could be just changed to

Private Sub Form_BeforeUpdate(Cancel As Integer)
 
    Dim stDocName As String
    stDocName = "frmConsumablePrompt"
    DoCmd.OpenForm stDocName, WindowMode:= acDialog, OpenArgs:= strConsumable
    If Currentproject.AllForms(stDocName).IsLoaded Then
        strConsumable = Forms(stDocName).strConsumable
    End If
 
End Sub

Where we have to retireve the value of one variable into the other.
That they have the same name doesn't mean anything otherwise. They're two distinct variable.
The above it the type of implementation that is common though.

Cheers.

 

[LPurvis]

By the way - I left the assignments of
strConsumable = yesand
strConsumable = no
Are they some constant or variable you've declared?
(Hence no complile error).

They imply you're just wanting a Boolean (Yes/No) result and so a Boolean variable would be more appropriate than a string.
There may be other options though - in which case you'd likely assign a string explicitly.
strConsumable = "yes"
strConsumable = "no"
strConsumable = "maybe"

 

[Lissa]

Nope.
DoCmd.OpenForm stDocName, WindowMode:= acDialog, OpenArgs:= strConsumable

I'm trying to pass the variable to the subform from the dialog form. strConsumable in the OpenArgs argument of the dialog form? It's null and it will be null until the user clicks yes or no. (crashing). I need to pass the variable that gets it's assignment in the dialog form back to an open subform so that the After_Insert event read that variable to determine how many times to insert a record.

This isn't working but I'll keep working on it and do more forum searching....

 

[LPurvis]

Nope?

The point of all that code above is to both pass the uninitialised variable to the called form (which as I said isn't so relevent to your situation but is worth knowing) and to collect the dialog form's result back.
That's why I went on to offer the rest of the code rather than just that one line of the example OpenForm method.

This is a very standard method, trust me.

 

[Lissa]

Sorry...it's not that I didn't trust your advice, it's just that I kept getting
"Run-time error '94': Invalid use of Null" at
Private Sub Form_Open(Cancel As Integer)
===> strConsumable = Me.OpenArgs
End Sub

After trying different things I found that if I created a module called Globals with
Public strConsumable As String
and removed this from the Before_Update event - it works.

If Currentproject.AllForms(stDocName).IsLoaded Then
strConsumable = Forms(stDocName).strConsumable
End If

I do appreciate all of your help and ideas. Thank you!

 

[gemma-the-husky]

try a different tack

if you need to know whether a PO Line is a consumable, then really you should store this information IN the orderl ine table, then you can have a yes no field for it in the form, and you don't need to do anything with it.

you could still ask about it in the before update event

ie your yesno field [consumable] can be set as simply as this one line

consumable = msgbox("Is This item a consumable ",vbyesno+vbquestion)=vbyes

 

[LPurvis]

The messagebox is probably a more straight forward implementation for you.

You've gone down the fully public variable method for now (which is a bit yuk but to be honest probably fair enough for your use for now - in time you can refine that).

The dialog form method is so common and standard though. There will be times when there are several values you want to return - not just a booean result. And you can only litter the application with so many global variables. ;-)
I don't have an example of the dialog in use per se that I can think of (I tend to try to offer examples of slightly different ways of doing things - but something this fundamental could do with inclusion somewhere I dare say...).

We'll see.

Cheers.

 

[Lissa]

Conclusion to this long story.... I was working on this database just before Thanksgiving holiday. I came back from holiday and ended up having to drop this project to work another. A couple of weeks ago I came back to this database.
Not sure how but my database ended up getting corrupt and I lost all the forms in my database and the majority of the code I had already written :-( (Lesson: Back up more often!!!)
I remembered this thread - came back to it and I was able to quickly rewrite the code. In the end I ended up going with gemma-the-husky's suggestion. For my purposes it works great and it's easier for my users. But I do also want to thank LPurvis for his suggestion. I learned some valuable information about variable handling since I really worked on his suggestions which is great since I still consider myself a novice VB programmer.
Thanks gemma and LPurvis!!!!

 

[gemma-the-husky]

i know youve dealt with this but reading it again, ...

if you need to classify what sort of purchase an order is for, then you need to choose whether this is done at the order (header) level, or at the the item (line) level - this depends on your own situation.

whichever you do decide the way to do this is surely to have an extra field/column in the header or line table called say purchasetype, keyed to a lookup table. Present this to your user as a bound combo box. and he will HAVE to pick an item from the drop down. thats the "normal" way to do it with normalised data

 

[Lissa]

Um.... okay - I'll read it again.
In my db - each line item in a purchase order could be a consumable item or a non-consumable item. If the item was not consumable, an x number of entries would be made into a part inventory table with a status of "On Order" where X = quantity entered. A consumable item would result in a single entry only. The part inventory table, the entry would also track serial numbers, part numbers among other things that get assigned by our QA guy after the part had been received and inspected.
The consumable field really just drove the number of entries to enter into the other table...