Public Parameters

[benc]

I have a problem with public variables, I tried exactly what Pat Hardman wrote in Public Variables as Parameters and found that my returnfunction would always be a null value even though my GV did have a value. here is my code:

MODULE:

Option Compare Database

Dim gvactype As String

Public Function Returngvactype() As String
Returngvactype = gvactype
End Function

FORM TO ENTER VALUE:

Option Compare Database

Private Sub searchparts_Click()

gvactype = Me.frmactype

MsgBox gvactype

'i have created the msgbox to makesure the values have entered

DoCmd.openform "FrmPartslistresults"

End Sub

QUERY IN WHICH TO RECALL THE VARIABLE:

WHERE ((([tblA/CParts].[Aircraft Type])=returngvactype())

Id be gratefull if you could help me with this many thanks.

 

[Pat Hartman]

You didin't do it quite the same way. Define the variable as Global if you will be referencing it (as you are) outside of the module in which it is defined.
Global gMultiSelect As String
ALL of your modules should contain the directive -
Option Explicit
Add it to all existing modules and change your db options so that it is added by default to each new module.

Your problem is caused by a combination of the above. Since you didn't define the variable as Global, it wasn't available in the form where you set it. This would have triggered an error, but, your code does not include the Option Explicit directive, therefore, VBA simply made a local variable with that name. That's why it seems to be set correctly in your message box. The difference is that the variable displayed in your message box is the local one that was defined for you behind the scenes rather than the Global one that you thought you were referencing. Specifying Option Explicit is simply good practice and will catch many scope problems such as the one you're having.

To verify my analysis, just add the Option Explicit to your form module. You will now get a compile error.

PS, you almost got my name right

 

[o1110010]

I tried exactly what Pat Hardman wrote.

LOL.

 

[benc]

Thanks that worked, and sorry about your name.

 

[benc]

I found a further problem that when i try to edit the results of the query in datasheet view they are un editiable. Am i not allowed to edit the results or is there something wrong

 

[o1110010]

Since alot of the source is from Pat.. and this post is a few days old now, you may want to Private Message him with your question. Unless by me adding this it gets re-noticed.

Good luck!

 

[Pat Hartman]

The selection criteria is not what makes a query non-updatable. You are probably using aggregate functions such as Sum() or Group By or the Distinct predicate. Another possibility is an improper join. If you post the SQL someone will help you.