Hi,

I'm coming from an ASP background, so maybe I'm going about this the wrong way.

I have Two forms:

In Form A, people make some selections, then I dynamically generate an SQL Query String using VBA and open up Form B, execute the String and place the resulting SUM in a Text field.

I've got everything working, except for figuring out how to call up the SQL query within VBA...! I've done some extensive Googleing, but cannot find a working answer. (I'm using Access 2003).

Should I be looking to Open Form B with my SQL query somehow, or should I open up an unbound form, run the Query in VBA (how), and then fill the text field with the correct value?

Can anyone help or point me in the right direction?

Thanx,

Augusta.

Hi Augusta,

If you are placing the result in a text field, why do you need the second form? Or do you mean a text box on the second form?

Does the following help:

Generate the SQL string as the caption of a label (it can be hidden) on Form A.
Click a command button on Form A to open Form B.
In the On_Open event of Form B put your VBA code. You will need something like this:
Dim db as Database
Dim rs as Recordset
Dim strResult as String
Dim strSQL AS String
strSQL = Forms!frmFormA!label1.caption
Set db = CurrentDB
Set rs = db.OpenRecordset(strSQL)
strResult = rs(0)
Me!Text1 = strResult




Clive Astley

Thanks Clive - you pointed me in the right direction.
With the above code I was able to find my other bug (incorrect syntax on the SQL Query itself).

You've just saved me countless hours of troubleshooting - I'm very greatful.

Augusta.

Glad you solved the problem Augusta.

Best wishes,


Clive Astley