View Single Post
  #4 (permalink)  
Old July 14th, 2009, 12:43 PM
mmcdonal mmcdonal is offline
Friend of Wrox
Points: 9,611, Level: 42
Points: 9,611, Level: 42 Points: 9,611, Level: 42 Points: 9,611, Level: 42
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
Join Date: Mar 2004
Location: Washington, DC, USA.
Posts: 3,069
Thanks: 0
Thanked 10 Times in 10 Posts

What are you doing with the recordsets once you get them?

To make the queries dynamic, you have to add the controls for your users to select parameter values.

For example, If I wanted a user to select a customer ID, I would create a combo box that was populated with valid customer ID's with some meaningful data like customer name displayed. Then when they clicked the button to run the report, if Customer ID was required, the code would be:

Dim lCustID As Long
Dim sSQL As String
Dim rs As ADODB.Recordset
Dim cn As ADODB.Connection
cString = conn() 'cString is a public variable, and conn() is a function that has my global connection information conn = "DSN=MyDsnName" for example.
If IsNull(Me.cboCustSelect) or Me.cboCustSelect = 0 Then
   MsgBox "Please select a Customer", vbInformation
   Exit Sub
   lCustID = Me.cboCustSelect
End If
sSQL = "SELECT * FROM tblCustomer WHERE CustomerID = " & lCustID
Set rs...
The only difference in your requirements is what type of query you are running determines how you process the resulting recordset.

If you are doing a select query, where do you want the data to go (form, report, table?)

If you are doing an insert, just opening the recordset does the insert. Do you want to check to make sure it was successful? The same with an Update or Delete query (I never allow my users to do those.)

Also, I would not process data while I was running the ADO connection, as with your date correction. I would bring my data over, and then apply the fix on the local recordset, or in the actual report Details section On Format event. Although THAT can take some time geven the amount of data. I would not call that function from the report, but put it in the report perhaps, or do a local Update query.

I would change the delete query to this(DELETE * FROM tbl_cupmmof), store it locally and then run this code:

DoCmd.SetWarnings False
DoCmd.OpenQuery "01a-qry_MakeCUPMMOF_d"
DoCmd.SetWarnings True
I am not sure what you are doing with the select query.

The Append query you show is an Insert query. You can take variables from a form for that and build the SQL string in your code from those variables as needed.

Can you post a single immediate issue that we can resolve? Then we can move on to the next...

Look it up at:
Reply With Quote