Switchboard Button to Run a Report 1

[SherryLynn]

Hello,

I am hoping someone can help me out. I am looking to add a button to my Switchboard to run a label report. Now here's the hard part (at least for me ).

To get the info for this report I have to run a make table query to get the info from particular records (there are parameters set up to meet certain criteria). Once I have this table, another query is run on this table which pieces some information together into a couple of columns. Then I have a label report which uses this query as it's source. How do I program a button on my switchboard to run all of this at once instead of doing two separate actions? I am not very experienced with coding. Thanks very much.

Sherry

 

[scriverb]

If you first query which is a make-table query were just a Select query that returns a recordset and that query is taken into your second query as the input rather than your table you problem is solved. Just use that last query as you RecordSource for the Label report and it will automatically run the first query for you to get the recordset it needs.

Post back if you have any specific questions. Post your SQL for the two queries if you wish and I will modify it to perform this function. Then you can just run the labels report and the correct data will be provided.

[COLOR=006633]Bob Scriver[/color]

[COLOR=white 006633]MSU Spartan[/color]

 

[SherryLynn]

Hello,

Ok, I tried changing my make table to a select query, but then I ran into a problem with the module that puts my data together.

The following module takes the info from the table that is "made" and pulls fields together. When I change the strSQL statement to reflect the select query instead of the table, I get the following error:
Run-time error 3061
Too few parameters. Expected 3

When I go to debug it highlights the Set rst. line

I'm not sure where to go from here. Can you help? Someone had once mentioned putting code behind the button to make all this run, but I'm not sure how to do that. I have posted the code for the module here. Thanks.

Option Compare Database
Option Explicit

Function getFamily(myID As Long) As String
Dim strSQL As String
Dim strHolder As String, strFam As String
Dim rst As DAO.Recordset
strSQL = "Select * From [tblLabels-Export] Where tblLabels-Export].[FamilyID]= " & myID
Set rst = CurrentDb.OpenRecordset(strSQL, dbOpenDynaset)
rst.MoveFirst
Do Until rst.EOF
strFam = rst![Family]
strHolder = strHolder & rst![Name] & ", "
rst.MoveNext
Loop
getFamily = UCase(strFam) & vbCrLf & Mid(strHolder, 1, Len(strHolder) - 2)

End Function

 

[scriverb]

I believe a missing left "[" is the culprety here:

 strSQL = "Select * From [tblLabels-Export] Where [b][red][[/red][/b]tblLabels-Export].[FamilyID]= " & myID

post back with the results.

[COLOR=006633]Bob Scriver[/color]

[COLOR=white 006633]MSU Spartan[/color]

 

[SherryLynn]

Hello,

Actually in my module it is there. I'm not sure why it didn't copy when I pasted the code here. Any other ideas?

Thanks,
Sherry

 

[scriverb]

What you have provided here is a Function. How are you calling this function? Is it from within a query? If so, please copy and paste that SQL here so I can see what is going on. Also, you may try taking the SQL string and running it in a query to see that the SQL executes okay.

Also, the criteria for the SQL string is comparing a field to a Long Integer value. Is the field [FamilyID] a long integer or AutoNumber?

 "Select * From [tblLabels-Export] Where tblLabels-Export].[FamilyID]= [red]xxxx[/red]"

Take the above SQl and just substitute a legitimate value for the Red x's and see if it will run by itself. Just copy and paste it into a query and run it. Let me know how it works.

[COLOR=006633]Bob Scriver[/color]

[COLOR=white 006633]MSU Spartan[/color]

 

[SherryLynn]

Hello there,

Thank you for helping me out here. The FamilyID field is an autonumber field. I am very inexperienced with VBA and am not sure where to go with this. Here is the SQL from the query. Thanks for all your assistance.

SELECT getFamily([FamilyID]) AS myFam, [queLabels-Export 2].FamilyID, [queLabels-Export 2].File
FROM [queLabels-Export 2]
GROUP BY [queLabels-Export 2].FamilyID, [queLabels-Export 2].File;

I had changed the make table query to a select query as you suggested but when I changed the references in the Function I got the error I mentioned in the last post. Help?

Sherry

 

[scriverb]

Like I said before it is the strSQL SQL string within your function that seems to be the problem. Let us just try to run that SQL all by itself and get it to work.

strSQL = "Select * From [tblLabels-Export] Where tblLabels-Export].[FamilyID]= " & myID

The above is the code from within the function. We need to create a new query for testing purposes and get that query to run independently. Copy and paste the SQL from my last posting into a query and replace the red x's with a valid FamilyID number and see if you can independently run the query. Does the same error crop up or does it run okay.

Post back with results.

[COLOR=006633]Bob Scriver[/color]

[COLOR=white 006633]MSU Spartan[/color]

 

[SherryLynn]

Hello,

Ok, I started a new query and pasted the sql you suggested, but I'm missing something. When you say to replace the "xxx"s what exactly should I replace into there? An actual FamilyID number? Sorry, I'm just a little confused on this one.

Thanks again for your patience with us "inexperienced" folk.

Sherry

 

[scriverb]

Yes, just type in a FamilyID number. It may be less than the number of x's I provided. Just clear them out and type in a number to see if the code will work. This is the SQL code that is not running in your Function statement.

[COLOR=006633]Bob Scriver[/color]

[COLOR=white 006633]MSU Spartan[/color]

 

[SherryLynn]

Hello,

I've been away for several days and haven't had a chance to get back here till now. Ok, I tried doing what you said, but it doesn't seem to work. I am getting errors. It doesn't matter what number I place in there, it still gives me errors. Any ideas?

Thanks very much.
Sherry

 

[scriverb]

Tell me what the errors say. We are just trying to execute the query as a stand alone. We need to make it work before using it in the rest of the code. Explain to me what you have done in detail and even post the code that you are using in your Query with the number inserted.

[COLOR=006633]Bob Scriver[/color]

[COLOR=white 006633]MSU Spartan[/color]

 

[SherryLynn]

Hi Bob,

I started all over and re-did what you said. Now the query is running with the sql you suggested and it gives me an accurate record. So, I guess the query has executed as a stand alone? Help.

Thank you,
Sherry

 

[scriverb]

Okay let's make this adjustment to the Function and try running the query again:

Function getFamily(myID As Long) As String
    Dim strSQL As String
    Dim strHolder As String, strFam As String
    Dim rst As DAO.Recordset
    strSQL = "Select * From [tblLabels-Export] Where [tblLabels-Export].[FamilyID]= " & myID
[RED]STOP[/RED]
    Set rst = CurrentDb.OpenRecordset(strSQL, dbOpenDynaset)
    rst.MoveFirst
    Do Until rst.EOF
    strFam = rst![Family]
    strHolder = strHolder & rst![Name] & ", "
    rst.MoveNext
    Loop
    getFamily = UCase(strFam) & vbCrLf & Mid(strHolder, 1, Len(strHolder) - 2)
End Function

When the first record is processed and the Function is called the code will stop on the command STOP. Now I am interested in what the variable strSQL equals. Also, the value of the variable [red]myID[/red].

You can get these values by holding your mouse pointer over the variables. The values will be displayed. As for the strSQL you can get the info by opening the Immediate Windows and typing: ?strSQL and then pressing Enter.

When you press the F8 key the code will walk through one line at a time. Do this until you get an error and then post the results.

[COLOR=006633]Bob Scriver[/color]

[COLOR=white 006633]MSU Spartan[/color]

 

[SherryLynn]

Hi Bob,

I did what you suggested and here is the sql statement:

Select * From [tblLabels-Export] Where [tblLabels-Export].[FamilyID]= 9418

myID = 9418

I didn't get any errors. What does this mean? Is that a good thing?

Thanks,
Sherry

 

[scriverb]

Now that we know that the query is working properly we want to continue and execute the Set RS command. For some reason it is not liking this statement. When you get to the STOP press the F8 key and it will continue to and then try to execute the next line. Press F8 again and see what happens.

Error?? or are you continuing to walk through the code?

[COLOR=006633]Bob Scriver[/color]

[COLOR=white 006633]MSU Spartan[/color]

 

[SherryLynn]

Hi Bob,

It continues through the code with no errors.

Sherry

 

[scriverb]

So now you are saying that all is working without the errors as you had before? If so, then remove the STOP and execute and see if this code now works for you. No idea why it wasn't working before since we didn't change anything and just walked through it by hand.

[COLOR=006633]Bob Scriver[/color]

[COLOR=white 006633]MSU Spartan[/color]

 

[SherryLynn]

Hi Bob,

Just looking back through our posts here. The function was working all along, until we tried changing it to a select query instead of a maketable. The problem I was trying to solve was how to get a button on the switchboard which runs all of this at once (my initial post). I'm thinking now that maybe a macro would work? Ideally I wanted only one query which would do all of what I described in the initial post. What do you think?

Thanks,
Sherry

 

[scriverb]

Well if we want to simply do as you wished this is sample code to be put behind the button to execute your make-table query and then run your labels program. You must change your Function back to what you had and make sure that the Select query that you originaly were using is the RecordSource for your lables report.

Put this behind a command button:

DoCmd.SetWarnings False
DoCmd.OpenQuery "[i][red]yourMakeTablequery[/red][/i]"
DoCmd.OpenReport "[i][red]yourLabelsReport[/red][/i]"
DoCmd.SetWarnings True

Good luck.

[COLOR=006633]Bob Scriver[/color]

[COLOR=white 006633]MSU Spartan[/color]