Solved Public variable (1 Viewer)

[estelleh]

I have a form - frmCustomer. If the user clicks on the "Search" button a new form - frmCustSearch opens, without frmCustomer being closed.

In the declaration section of frmCustomer, I declare a variable to store the customer code selected in frmCustSearch:

Public PassCustCode As Integer

When I try and assign a value to PassCustCode in frmCustSearch, I get a compile error - variable not found.

I thought declaring a variable as public made it visible in the entire project?

 

[arnelgp]

you put the Public variable in a Module not in the Form module.

 

[Ranman256]

Do not put public vars in forms.
Put them in modules.

 

[Galaxiom]

BTW Your original Public variable in the form module would be referred to from outside the form as a member of the form object:
fmCustSearch.PassCustCode

Generally though, variables inside objects are better presented as Properties of the object. A Public variable can be read or written using the reference I showed. As a Property you have separate Get and Let subs to address a Private variable.

A read only Property as you would usually have in this situation (because it is only generated inside the form) would only have a Property Get and cannot be written to from outside the form. A Let Sub can also include validation routines that control what can be written to the variable.

Many developers use Public variables in Standard Modules (often called Global Variables) much more often that they really need to. If you wouldn't need the value again after the search form was closed it would be better as a Property of that form than as a global variable.

I hope this makes sense.

 

[estelleh]

Thank you! I have been somewhat confused as to where each bit of code is kept, but now I've found the Project Explorer

So now I have the CustCode value of the record the user selected in the search form. I close the search form and the Customer form is still displayed (I don't close it when the user selects "Search"). I have tried to change the Customer form Recordsource in the Select Button Click event of the Search form (because there is no event triggered by the Customer form), but I get an error that frmCustomer can't be found - although it should still be open?

If lstCustName.ListIndex = -1 Then
    MsgBox "Please select a customer", vbOKOnly
Else
    DoCmd.Close
    PassCustCode = lstCustName
    Forms!frmCustomer.RecordSource = "SELECT * from Customer where CustCode = PassCustCode"

Even worse, if I put a breakpoint at the beginning of the sub and run it step by step, I get an error 2450 - "Microsoft Access cannot find the referenced form 'frmCustomer' ", but if I put the breakpoint on the error handling MsgBox itself or take away all breakpoints, I get error 2467 -" The expression you have entered refers to an object that is closed or does not exist"

@Galaxiom I think your explanation makes sense. As soon as I sort out the above problem I'll try it the way you explained.

 

[Gasman]

You cannot just use PassCustCode like that, you need to concatenate with the Select string
You also cannot use any form controls if you have closed the form?

 

[estelleh]

You cannot just use PassCustCode like that, you need to concatenate with the Select string
You also cannot use any form controls if you have closed the form?

I thought it looked strange.... So

"SELECT * from Customer where CustCode = " & PassCustCode

?

However it's not even getting to the Select statement by the looks of it.

I am closing the Search form. The change record source refers to the Customer form which hasn't been closed. If I comment out the recordsource line, the Search form closes and the Customer form is still open - still displaying the original record, not the newly selected one - hence the recordsource code.....

 

[Gasman]

If i was doing this, I would set the variable, close the search form and concatenate the variable with the select in the cust form button code?
Not something I have ever tried, but you are closing a form from where the code is running ?

 

[estelleh]

You cannot just use PassCustCode like that, you need to concatenate with the Select string
You also cannot use any form controls if you have closed the form?

I just realised I close the form before assigning the variable

If i was doing this, I would set the variable, close the search form and concatenate the variable with the select in the cust form button code?
Not something I have ever tried, but you are closing a form from where the code is running ?

I just realised I close the form before assigning the variable ... sigh.... that's fixed the error message at least!

concatenate the variable with the select in the cust form button code

This is my problem - when the search form closes, there doesn't seem to be any triggered event where I can put the SELECT statement to update the Customer for to the selected customer.

I have done a work around by closing the Customer form, and re-opening it with the Select statement, but I'd still like to know how to change the displayed record without closing and re-opening the form.....

 

[MajP]

Looking at your error, I have to believe you are wrong. If frmCustomer is truly open and your code gives you an error that it cannot be find, You probably have a spelling error. The form is not called frmCustomer. Maybe frmCustomers, or formCustomer, or frmCstmr. Check your spelling. If it is a subform then you have another issue. But if both forms are open no need for a variable. You can change the recordsource like your are doing or move to the record without changing the recordsource.

However I would reorder the code and never use Close without defining what to close

If lstCustName.ListIndex = -1 Then
    MsgBox "Please select a customer", vbOKOnly
Else
   'only if frmCustomer is really open
   Forms!frmCustomer.RecordSource = "SELECT * from Customer where CustCode = " & me.lstCustName
   'you should not need a requery, but in rare cases you can add
   Forms!frmCustomer.requery
   'now close the search form
   docmd.close Acform, me.name
end if

to move to the record instead

Forms!frmCustomer.Recordset.findfirst  "CustCode = " & me.lstCustName

 

[estelleh]

Looking at your error, I have to believe you are wrong. If frmCustomer is truly open and your code gives you an error that it cannot be find, You probably have a spelling error. The form is not called frmCustomer. Maybe frmCustomers, or formCustomer, or frmCstmr. Check your spelling. If it is a subform then you have another issue. But if both forms are open no need for a variable. You can change the recordsource like your are doing or move to the record without changing the recordsource.

However I would reorder the code and never use Close without defining what to close

If lstCustName.ListIndex = -1 Then
    MsgBox "Please select a customer", vbOKOnly
Else
   'only if frmCustomer is really open
   Forms!frmCustomer.RecordSource = "SELECT * from Customer where CustCode = " & me.lstCustName
   'you should not need a requery, but in rare cases you can add
   Forms!frmCustomer.requery
   'now close the search form
   docmd.close Acform, me.name
end if

to move to the record instead

Forms!frmCustomer.Recordset.findfirst  "CustCode = " & me.lstCustName

That is so weird - the form is definitely called frmCustomer - and your code works perfectly, yet mine crashes.... Well hell.... I'll take it though - thanks!

 

[MajP]

That is so weird - the form is definitely called frmCustomer - and your code works perfectly, yet mine crashes.

The only thing that would explain that, since I basically copied and pasted your code, would be that it was stuck in a decompiled state with some garbage left in. So although your code appeared correct it was not compiling the correct thing. When you pasted my code it cleared it out. You may want to consider doing a decompile and recompile or your code to be safe and avoid potential corruption.

Access Keeps Crashing when I go click Debug

Hi, I have just been working on Crosstab queries and reports with parameters and have got it all working (or so I thought). I am now getting an error on the Querydef function where I am setting up the parameters of the query in VBA before calling the report. I have no idea why this isn't now...

www.access-programmers.co.uk

Decompile and Compact Your Microsoft Access Database to Improve Performance and Fix Corruption

Decompile and Compact Your Microsoft Access Database to Improve Performance and Fix Corruption

www.fmsinc.com

 

[estelleh]

That is so weird - the form is definitely called frmCustomer - and your code works perfectly, yet mine crashes.

The only thing that would explain that, since I basically copied and pasted your code, would be that it was stuck in a decompiled state with some garbage left in. So although your code appeared correct it was not compiling the correct thing. When you pasted my code it cleared it out. You may want to consider doing a decompile and recompile or your code to be safe and avoid potential corruption.

Access Keeps Crashing when I go click Debug

Hi, I have just been working on Crosstab queries and reports with parameters and have got it all working (or so I thought). I am now getting an error on the Querydef function where I am setting up the parameters of the query in VBA before calling the report. I have no idea why this isn't now...

www.access-programmers.co.uk

Decompile and Compact Your Microsoft Access Database to Improve Performance and Fix Corruption

Decompile and Compact Your Microsoft Access Database to Improve Performance and Fix Corruption

www.fmsinc.com

Thanks @MajP - will do that!