Using global variable in update query

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Adrian - 24 Nov 2006 04:03 GMT

Hi,

I'm no programmer, but am trying to add what seems to me to be a fairly
simple feature to a very simple database I have put together. On the click on
a button on a form, it calls a couple of reports to print an exam built up of
questions and the corresponding marking guide flagged by a user, then uses a
query to clear the flag and mark the current date in a "last used" field for
each question used. On clicking this button, I prompt the user for a title to
use on for the exam (called GBL_ExamText), and I am happily feeding that into
the reports, but what I can't seem to do is get this user value to update to
the database so I would have both the date any given question was used and
the exam name (as entered by the user) it was used for.

The SQL view of the query is:

UPDATE Data SET Data.UseInTest = No, Data.DateLastUsed = Now(),
Data.LastUsedComment = GBL_ExamText
WHERE (((Data.UseInTest)=Yes));

As it is, this prompts the user again for an entry for GBL_ExamTest, but the
value I want to add is in by this stage (as evidenced on the reports that are
produced) so clearly it's a case of the query not accessing the variable
correctly.

The other two updates work perfectly.

Anyone got any suggestions?!

Cheers
Adrian

Reply to this Message

Tom Wickerath - 24 Nov 2006 05:25 GMT

Hi Adrian,

Try using a wrapper function to retrieve the value of your global variable.
First, create the function in a new stand-alone module:

Option Compare Database
Option Explicit

Dim GBL_ExamText As String

Function GetGlobal() As String

If Len(GBL_ExamText) = 0 Then
GBL_ExamText = "Uninitialized"
End If

GetGlobal = GBL_ExamText
End Function

Then call this function in your update query:

UPDATE Data SET Data.UseInTest = No,
Data.DateLastUsed = Now(),
Data.LastUsedComment = GetGlobal()
WHERE (((Data.UseInTest)=Yes));

Tom Wickerath
Microsoft Access MVP

http://www.access.qbuilt.com/html/expert_contributors.html
http://www.access.qbuilt.com/html/search.html
__________________________________________

> Hi,
>
[quoted text clipped - 26 lines]
> Cheers
> Adrian

Reply to this Message

Adrian - 27 Nov 2006 00:50 GMT

Ah, thanks Tom.

I knew it had to be something simple!

Cheers
Adrian

Reply to this Message

Tom Wickerath - 27 Nov 2006 00:56 GMT

Hi Adrian,

Glad to hear that you got it working. In the original example, I showed this:

If Len(GBL_ExamText) = 0 Then
GBL_ExamText = "Uninitialized"
End If

You could also call some function to re-initialize your global variable, in
the event that it's length was zero:

If Len(GBL_ExamText) = 0 Then
GBL_ExamText = InitializeGBL
End If

where InitializeGBL is the name of another function used to initialize the
value of your global variable.

Tom Wickerath
Microsoft Access MVP

http://www.access.qbuilt.com/html/expert_contributors.html
http://www.access.qbuilt.com/html/search.html
__________________________________________

> Ah, thanks Tom.
>
> I knew it had to be something simple!
>
> Cheers
> Adrian

Reply to this Message