Assigning a VBA result in a Report to a field in a Table

  • Thread starter Thread starter Scott Albright
  • Start date Start date
S

Scott Albright

I have VBA running behind a report that calculates the registration status of
each student based on many criteria. My VBA sub runs fine and produces the
desired result.

The problem: I want to be able to store that result with the student's record.

Question: How do I, within my VBA sub, assign the calculated string value to
the student's record. Or, do I have to do it another way?

Thanks in advance for your time and expertise.
 
I have VBA running behind a report that calculates the registration status of
each student based on many criteria. My VBA sub runs fine and produces the
desired result.

The problem: I want to be able to store that result with the student's record.

Question: How do I, within my VBA sub, assign the calculated string value to
the student's record. Or, do I have to do it another way?

Thanks in advance for your time and expertise.
Click to expand...

Unless the calculation is extraordinarily complex, you probably shouldn't! It
sounds like the registration status can change with time, based on these many
criteria; if you store the current status today it may well be *wrong*
tomorrow, or next month. It's probably best to simply create a Query calling
your VBA function and dynamically calculate the status whenever it's needed.

If you have a good reason to store it (e.g. to capture the status as of a
point in time, for future reference even if the status is then different) then
your best bet is to run an Update query on the table, putting a call to your
function on the Update To line.
 
Im no expert but id use an update sql query

i.e docmd.runsql "UPDATE Users SET Users.[Print Label] = """ & Variable &
""" WHERE Users.[Print Label]=Yes;"

you said your variable was a string to use a striong variable in an sql
query you must use

""" & variable & """
 
So, just to clarify. I need to change the private sub that's running under my
report to a public sub or function and then call that public sub/function to
the query or run an update query calling the sub/function to the Update To
field.

Is this correct?

Thanks

Joseph Atie said:
Im no expert but id use an update sql query

i.e docmd.runsql "UPDATE Users SET Users.[Print Label] = """ & Variable &
""" WHERE Users.[Print Label]=Yes;"

you said your variable was a string to use a striong variable in an sql
query you must use

""" & variable & """


Scott Albright said:
I have VBA running behind a report that calculates the registration status of
each student based on many criteria. My VBA sub runs fine and produces the
desired result.

The problem: I want to be able to store that result with the student's record.

Question: How do I, within my VBA sub, assign the calculated string value to
the student's record. Or, do I have to do it another way?

Thanks in advance for your time and expertise.
Click to expand...
Click to expand...
 
So, just to clarify. I need to change the private sub that's running under my
report to a public sub or function and then call that public sub/function to
the query or run an update query calling the sub/function to the Update To
field.

Is this correct?
Click to expand...

Yes.

Would it not have been as quick to try it as to post the question and wait for
the answer?
 
I'm thinking the final question was rhetorical, but in case it was not, yes
and no.

I was hoping I wouldn't have to transfer my private sub feeding off text
boxes in a report to a global function.

But I did and I have and it works perfectly.

Thank you.

You guys rock!
 
Back
Top