Question Using a Global to Populate a Table Value

[txgeekgirl]

Good Afternoon - I have a global variable populated and need to feed that value to a table. Would the best way to do that be to write a SQL Update statement or is their a better way?

 

[boblarson]

If you have a variable then you can write an insert SQL statement and use that variable within it (properly outside of the quotes) and then use CurrentDb.Execute to do it.

Although you can also use it in a saved query but if you do then you need to create a wrapper function to return the value of the variable.

 

[txgeekgirl]

Here is what I am troubleshooting:

Prelim = DLookup("YesScore", "PrelimCalcSelectQuery", "[AuditID] = " & Me.MyAudit)
    If DLookup("PrelimScore", "Audit", "[AuditID] = " & Me.MyAudit) = 0 Then
        mySql = "UPDATE Audit SET PrelimScore = " + Prelim + _
            " WHERE Audit.AuditID = " + Me.MyAudit + ";"
        DoCmd.RunSQL mySql

 

[txgeekgirl]

OMG! Mr. Larson - you are good luck. Don't know why it worked - but that code actually gave me no more errors and populated my my table just fine.

 

[boblarson]

Is PrelimScore a numeric or a text field?

Also, you should be using the ampersand (&) instead of plus signs (+) for concatenation within Access. Also, your DLookups should be using the NZ function to handle nulls returned.

 

[txgeekgirl]

I know - I am horrible about that - in VFP they like + and VB likes &.

Thank you so much!

 

[boblarson]

OMG! Mr. Larson - you are good luck.

Now if only I could be the beneficiary of that luck. But alas, on my side I seem to be cursed with BAD luck more so than good.

Perhaps it is some sort of offset, eh?

 

[txgeekgirl]

STOP -

I absolutely will not agree with that but will be sending good thoughts your way!