Experts- Need Knowledge & Advice

[modest]

I have a function in a module that runs a query and prints to a form's text box.Let's look at an example.

function dostuff( variable as Variant )
... (code to do the query)...
variable = rs![field name] 'rs is the recordset created
end function


if i call the following from a button's onclick( ), the textbox on the form will not be populated:
dostuff Me.textbox


However if I do: dostuff Me and in the dostuff() function i change the line to variable.textbox = rs![field name], everything will work fine. Why is this AND how do I reference the textbox from the function so that i can make this more dynamic.

 

[Mile-O]

In which module is the function? If it's in the Class module of the form then you can use Me. If it's in a standalone module or Class Module then you can't use the Me.

It would be more helpful if you just posted the actual code you have rather than skipping around some pseudo code and encouraging guesswork.

The reason I say this is that dostuff Me.textbox is a syntax you would use if you were to be calling a Subroutine. Functions return a value and require you to assign them to something.

 

[dt01pqt]

It is better practice not to use variant where possible use control or textbox instead.

 

[modest]

I will post my code, just thought this would be an easy explanation (which is why I tried to simplify). This is what's in the Module and no, there isn't a return value from the function, it could be a subroutine if I wanted it to:

Public Function FindCityState(table As String, cityField As Variant, stateField As Variant, frm As Form)
    Dim db As DAO.Database                      'declare variable to hold db/workspace name
    Dim qdf As DAO.QueryDef                     'declare variable to hold query info
    Dim rs As DAO.Recordset                     'declare variable to reference query result table
    
    Dim strSQL As String                        'declare sql string variable
    Dim queryName As String                     'declare query name variable
    
    Set db = CurrentDb                          'Set name of database
    queryName = "tempQuery"                     'Set name of temporary query
    
    strSQL = "SELECT DISTINCT [3 CITY],[3 ST] " & _
             "FROM " & table & " " & _
             "WHERE (" & table & ".[5 CITY]='" & cityField & "' " & _
             "AND " & table & ".[5 ST]='" & stateField & "');"
    
    If Not QueryExists(queryName) Then
        Set qdf = db.CreateQueryDef(queryName)
    Else
        Set qdf = db.QueryDefs(queryName)
    End If
    
    qdf.SQL = strSQL                            'Match the SQL to the query name
    
    Set rs = db.OpenRecordset(queryName)
    frm.OriginCity = rs![3 CITY]
    frm.OriginState = rs![3 ST]
     
    rs.Close
    Set rs = Nothing
    db.QueryDefs.Delete queryName
    
End Function

And this is what's in the button's event from the form:

Private Sub Submit_Click()
    Dim db As DAO.Database                      'declare variable to hold db/workspace name
    Dim qdf As DAO.QueryDef                     'declare variable to hold query info
    Dim rs As DAO.Recordset                     'declare variable to reference query result table
    
    Dim strSQL As String                        'declare sql string variable
    Dim queryName As String                     'declare query name variable
    Dim table As String                         'declare table name variable
    
    
    Set db = CurrentDb()                        'Set name of database
    queryName = "tempQuery"                     'Set name of temporary query
    table = "[AGGREGATION TABLE]"               'Set name of the table
    
    FindCityState table, _
                  [Forms]![Main]![oCity].[Value], _
                  [Forms]![Main]![oState].[Value], _
                  Me
End Sub

notice how I sent "Me" in, but could not do "Me.OriginCity" and have a variable catch it as a parameter. My friend says he thinks it's because Variant doesn't hold objects, just data values and when you call Me.<textbox> all it passes in is the current value of the textbox.

 

[Mile-O]

In which module is the function? If it's in the Class module of the form then you can use Me. If it's in a standalone module or Class Module then you can't use the Me.

It would be more helpful if you just posted the actual code you have rather than skipping around some pseudo code and encouraging guesswork.

The reason I say this is that dostuff Me.textbox is a syntax you would use if you were to be calling a Subroutine. Functions return a value and require you to assign them to something.

 

[modest]

The function is in a standalone module... the button is in a form's sub.

In addition, what I posted above already works. So I CAN pass "Me" to a standalone's function. But what I want to do is just pass the textbox object and not the whole form so I can set any textbox equal to the result of the query from outside the function.




** Found my error

Friend was right (and Dt01pqt too), the problem was I was declaring variable as a Variant, which I overlooked and changed to Object. Everything's all good now. Thanks for the help guys!

Sorry about overlooking your post Dt, I had to go on msdn and actually look at what a Variant could hold. I thought it just dynamically matched any variable type at run-time instead of when compiling.

To others who made the same stupid mistake as me: parameter variable can be defined as type "Object" or "TextBox" interchangeably.

-modest

 

[Mile-O]

FindCityState should still be a sub and not a function.

 

[modest]

hahaha.. you're talking to someone who is more familiar with c++
I get to VB and i'm like "sub?" but yes you're probably right. Thanks again for help in this post and others SJ

-modest

 

[dt01pqt]

Sorry about overlooking your post Dt, I had to go on msdn and actually look at what a Variant could hold. I thought it just dynamically matched any variable type at run-time instead of when compiling.

To others who made the same stupid mistake as me: parameter variable can be defined as type "Object" or "TextBox" interchangeably.

-modest

Ah don't worry about it modest I'm used to it. If you use control instead of object it should take up less memory. Also use 'control.value =' rather than just 'control ='.

 

[modest]

If you use control instead of object it should take up less memory. Also use 'control.value =' rather than just 'control ='.

Control does work as well and I've made many changes to my code. This was just introductory stage. I do use the .value method , but instead of having a textbox for a state and one for city... I have just one box and use a string to improve formatting.

If I need that particlar city or state from the textbox, I have used a comma delimiter to separate the two and split them up using the instr() and $mid() functions.


Regardless everything is coming along nicely.

-modest

 

[Mile-O]

instead of having a textbox for a state and one for city... I have just one box and use a string to improve formatting.

If I need that particlar city or state from the textbox, I have used a comma delimiter to separate the two and split them up using the instr() and $mid() functions.

If you had a list of cities you could have a table like this:

tblCities
CityID
City
StateID

with StateID being related to the PK in

tblStates
StateID
State

A query in a combobox could return:

SELECT CityID, City & ", " & State AS Location
FROM tblCities INNER JOIN tblCities.StateID ON tblStates.StateID
ORDER BY City & ", " & State;

So, the user is only actually selecting the CityID but through which you can get the StateID anytime due to the correct relationships. No need for breaking down non-atomic data.

All a user would need to do is pick the city.

 

[modest]

Hmmm that's interesting, but the list of cities and states are already generated. This is sort of like a mapquest for businesses designed in my company's interest, if you will. There's a city/state of origin and destination, which the user types into 4 textboxes.

The db matches those to other locations from a table. So the origin and destinations are matched with the closest facilities that my company conducts business.

For readability the closest places are echoed to the form. Originally I printed them to 4 separate textboxes, but what I was saying is that now i have them going to 2 with commas to separate city and state.

To do more queries based on the closest facility textbox origin and destination, I don't believe I can break down the string through SQL the way you have suggested, so i broke them down into separate variables.


If I'm wrong let me know,
modest