query criteria from active form

[dylan_dog]

Hi guys,

I have a form that displays information in a subform (manufacturer, serial number and inventory number) about slot machines that I need to repair. When I double-click on a machine, another form comes up, based on the manufacturer. I use those forms to input the counters for that slot. That means I can have six different forms (but only one at the time). They are identical, only labels are different, because each manufacturer has their own names for them. After I save those counters to a table, I'd like to print out a report with those counters on it. I know I could create six queries, but is there a way to use just one query and pass the textbox name from active form as a criteria for the query?

Regards, Samo

 

[vbaInet]

The same way you're passing the ManufacturerID from one form to the other will be the same way you would pass the relevant ID to your report. How are you doing the former at the moment?

 

[dylan_dog]

I haven't made the report yet, but that is not the problem. Once I get the query running from any of the forms, I'll just base the report on that query and pick the right report based on the manufacturer. Attached is the picture of my project, query1 should get the serial from either frm_Brojcanik_IGT or from form qry_Brojcanik_Novomatic (that one isn't finished yet and they will be modal to prevent opening two at the same time).

 

[dylan_dog]

Sorry, I forgot that it isn't in English. serial is "serijski", manufacturer is "proizvodac", inventory numbers are "pit" and "broj", "igra" is game.

 

[vbaInet]

So what is the problem then?

 

[dylan_dog]

The problem is criteria for that query. How can I tell the query to take input for serial number from any of those six forms. I can't use forms!formname!textboxname because I don't know which form will be calling the query. If I understand correctly, I should somehow create a variable that will hold the name of the active form, then create a function with that variable and pass that to the query as a criteria. textboxname is the same on all forms.

I already tried this method (Mr. Larson explained it on this forum in a thread (www).access-programmers.co.uk/forums/showthread.php?t=231088), but I get the error that the form isn't found.

 

[vbaInet]

Alright, I follow. But let's clarify two things:

1. will there be only one of those forms open at any given time?
2. Are the field names and data types that you are referencing the same across the forms and the query?
2. How many forms are there?

 

[dylan_dog]

Hi,

yes, only one form will be open at the time (they will be modal) and yes, field names are the same on all the forms, text box "serijski". Data type is text, because those serials aren't all composed from numbers. At this moment there are six forms, but I'll add two more dummy forms if some other manufacturer sells us something new. So, eight forms.
Regards, Samo

 

[vbaInet]

I will tell you the steps and give you some aircode.

Steps:

1. Save the names of the forms into an array
2. Loop through array in step 1 and for each item of them check whether it is open
3. If an open form is found use get the value and save it into a variable for use in your query later.

Aircode:

dim strFormNames(1 to 6)  as string
dim i as integer

strFormNames(1) = "[COLOR=Red]FormName1[/COLOR]"
strFormNames(2) = "[COLOR=Red]FormName2[/COLOR]"
strFormNames(3) = "[COLOR=Red]FormName3[/COLOR]"
[COLOR=Red]... and so on ...[/COLOR]

for i = lbound(strFormNames) to ubound(strFormNames)
    if Application.SysCmd(acSysCmdGetObjectState, acForm, strFormName(i)) = acObjStateOpen Then
        varWhereValue = forms(strFormName(i)).[COLOR=Red]TextboxName[/COLOR].value
        exit for
    end if
next

varWhereValue will be a global variable that will be called by a function called GetWhereValue, i.e.:

function GetWhereValue() as variant
    GetWhereValue = varWhereValue
end function

... then in your query criteria you will simply put GetWhereValue() there.

Of course it will fail when there's no value in the textbox so you need to cater for that.

All you need to edit for now are the bits in red.

 

[dylan_dog]

Please bear with me .

I copied your code like this to module1:

Public Sub samo()

Dim strFormNames(1 To 2) As String
Dim i As Integer

strFormNames(1) = "frm_Brojcanik_IGT"
strFormNames(2) = "qry_Brojcanik_Novomatic"
'strFormNames(3) = "FormName3"
'... and so on ...

For i = LBound(strFormNames) To UBound(strFormNames)
If Application.SysCmd(acSysCmdGetObjectState, acForm, strFormNames(i)) = acObjStateOpen Then
varwhereValue = Forms(strFormNames(i)).serijski.Value
Exit For
End If
Next

End Sub

Public Function GetWhereValue() As Variant
GetWhereValue = varwhereValue
GetWhereValue = varwhereValue
End Function

It compiles OK, but the query comes up empty. I think that my problem lays in step 1. I put a break point to the for... part of the code and it never gets there. Where should I put that code where you say I must save table names as array?

 

[vbaInet]

1. Why do you have a duplicate of GetWhereValue = varWhereValue?
2. Where did you declare varWhereValue?

 

[dylan_dog]

Hi,
it seems that we finally made it . Below is the code that works, it is in standard module.

Public Function GetWhereValue()

Dim varwherevalue As String
Dim strFormNames(1 To 2) As String
Dim i As Integer

strFormNames(1) = "frm_Brojcanik_IGT"
strFormNames(2) = "frm_Brojcanik_Novomatic"
'strFormNames(3) = "FormName3"
'... and so on ...

For i = LBound(strFormNames) To UBound(strFormNames)
If Application.SysCmd(acSysCmdGetObjectState, acForm, strFormNames(i)) = acObjStateOpen Then
varwherevalue = Forms(strFormNames(i)).[COLOR=Black]serijski[/COLOR].Value
Exit For
End If
Next
   
    GetWhereValue = varwherevalue
    MsgBox varwherevalue
End Function

vbaInet, thank you very much for your help and patience,

Samo

 

[vbaInet]

You're welcome Samo!