How to Pass a VB variable on to a query? 1

[ddelport]

I have two different databases with tables that have similarities. The first database has a table with "temporary" information. The second database is used for "formal" information. It is in a invironment where you would typicaly have a customer enquiry, you would therefor take down just some critical information like Surname, Initials and contact numbers etc. This kind of info goes into the first mentioned. Once the customer engage in formal business, there would be more information that has to be captured from an application form (in the second database).

Initialy there was a unique temporary Id that will now be entered in the new database table. My idea is to place the entered value in a variable through visual basic, excuse my amateurish code but it looks something like this:

Private Sub TempRecord_GotFocus()
Dim IdNr As String
IdNr = [Tempid]
DoCmd.OpenQuery "Query1"
End Sub

Now Query1 is an update query that has to automaticaly update the respective fields in the "formal" table. My problem is that my access query doesn't "see" the variable,
Why?

Example of query:

UPDATE Table1, NewClients SET Table1.record = [NewClients]![NAME]
WHERE ((([NewClients]![TEMP_ID])=[:IdNr]));

 

[RoyVidar]

You'll need to declare the variable as public in a module (not a forms module), then you can retrieve it and use it within a query with a public function.

[tt]Public gstrMyGlobal as String

Public Function GetgstrMyGlobal() as String
GetgstrMyGlobal = gstrMyGlobal
End Function[/tt]

Then in a query, put GetgstrMyGlobal() in the criteria row.

- replace my names with yours, and I recommend some naming convention showing that this is a global, prefix with g and the datatype?

Roy-Vidar

 

[ddelport]

Thanks Roy-V!

I have tried it. The function seems to work just fine but I don't know why I get this runtime error: "Undefined function in Expression" Maybe I am missing a step somewhere? I have written a seperate module with the following:

Option Compare Database

Public MyValue As Integer

Public Function GetMyValue() As Integer

MyValue = Forms!Formal!TempID & Me

MsgBox MyValue

GetMyValue = MyValue

MsgBox MyValue

End Function

(I even threw in that bizarre little Me thingy, why I don't know but my intuition told me to. What will happen at runtime if two seperate users run the same process at the same time, how will the variable's value be affected?)

The messageboxes is just a temporary control system for myself to see if the correct value is appointed to the variable at the right instance....

The next step is an event that's suppose to run the required query but that's when I get the problem that the query (as I understand it in my own words) don't recognise the function.

The event procedure looks like this:

Private Sub Name_GotFocus()

DoCmd.RunSQL "UPDATE Formal, Temp SET Formal.Name = Temp.Name, Formal.[Number] = Temp.Number WHERE (((Formal.TempID)=GetMyValue())); ", -1

End Sub

I first ran the query via a DoCmd and then tried to run the SQL directly but it has the same effect: Not the one I am looking for.

Could you please be as kind a to give me some pointer to where I am going wrong. I am still more than somewhat of a Baboon@VB so forgive me if I ask silly questions.

 

[RoyVidar]

Hi again!

In the first post you where trying to run a stored query. In a stored query variables cannot be passed directly, but need to be fetched thru a function.

The idea of using such, implies
1 - having a public variable
2 - assigning a value to that variable (prior to invoking the function)
3 - invoke the function (run the query which invokes the function)

This is consistent with my example, there's only one line in the function, assigning the public variable to the function as the return value.

The assigning of the value should be done prior to invoking the function i e in/on the line before opening the query

[tt]gstrMyGlobal = Me!txtWhatEverControlHoldingThe.Value[/tt]

In your reply you are doing something quite different, you're using the RunSql method of the DoCmd object to run an update vba sql string - this is a completely different animal. Using such, no public variables/functions are needed. One can use variables, form references, functions..., onle ensure that the values are concatinated into the string, not the name of the variable/function or the reference (as you do with the function). How - here are three examples, showing how to create vba sql string criterias with numbers, strings and dates (just using plain select to illustrate):

Numeric:
[tt]"Select * from mytable where MyNum = " & Me!txtSomeNum.Value[/tt]

- note the concatination of the string and the variables, and since it's numeric, no qualifyers are needed

String:
[tt]"Select * from mytable where MyText = '" & Me!txtSomeText.Value & "'"[/tt]

- text qualifiers, apostrope/single quote surrounding the form reference

Date:
[tt]"Select * from mytable where MyDate = #" & Me!txtSomeDate.Value & "#"[/tt]

- date qualifyers are hash (#)

Another note on dates and vba sql strings, if your regional settings have dates differing from the US date format, you'll also need to format the date. I'll recommend the ansi format, then the prior string (shortened), might look like this:

[tt]...MyDate = #" & format$(Me!txtSomeDate.Value,"yyyy-mm-dd&quot & "#"[/tt]

A very good tip here, is to declare a string variable to hold the sql statement, assign the statement and print it to the immidiate pane.

[tt]dim sSql as String
sSql="Select * from tblTest where id = " & Me!txtID.Value
Debug.Print sSql[/tt]

Then hit Ctrl+G to get to the immidiate pane, where you can either study how the actual sql statement looks, or copy it and paste it into the SQL view of the QBE and run it. This is a very good way of testing the sql strings.

Roy-Vidar

 

[ddelport]

Thank You Roy-Vidar! To be honest: I took this long because it took me THAT long to completely process and file the last posting you sent me. This was worsened by the fact that the saved Access query still complained about an "undefined function". Well, my (and your's thankfully) persistance paid off - After a very painfull soul search I realised that I had to include "()" with my module name - Just don't ask me why because I still don't understand why in the world, I have defined the function in the module completely then? Main thing is that it remedeed my frustration!

This question of mine proofed once again to make me learn more that what I bargained on, so a BIG THANK YOU to Roy-Vidar! You deserve a star!