UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> Run-time error 7874 - on a SELECT query    
 
   
MrChips
post Nov 12 2010, 08:39 AM
Post#1



Posts: 387
Joined: 3-July 05
From: Tonbridge, Kent, England


I want to run a report from VBA on a button command40... based upon a WHERE parameter in the query which will use a combo box on the same form as command40.
can't do it!
Ocan't even get this to run without 'run-time error 7874. Microsoft Access can't find the object 'SELECT full_name FROM t2_members.'
The spelling is correct, the table is not linked, and the field [full_name] exists in the table [t2_members] in the same database.
If I can't get the below to work ... what chance is there for me?
Any advice will be gratefully received, when hopefully I will be able to build up to achieving the first line above!
#######################
Private Sub Command40_Click()
Dim sql As String
sql = "SELECT full_name FROM t2_members"
DoCmd.OpenQuery sql
End Sub
#######################
Go to the top of the page
 
Doug Steele
post Nov 12 2010, 09:02 AM
Post#2


UtterAccess VIP
Posts: 21,669
Joined: 8-January 07
From: St. Catharines, ON (Canada)


From DoCmd.OpenQuery Method, the QueryName mu
On other words, you can't use SQL like that.
However, your post states "I want to run a report from VBA on a button command40". Why are you trying to run a query?
Go to the top of the page
 
MrChips
post Nov 12 2010, 09:23 AM
Post#3



Posts: 387
Joined: 3-July 05
From: Tonbridge, Kent, England


Oh dear ... I don't think that I quite understand that.
... but I think it means that I have to have the query working elsewhere, which I do. called q2_charity_statement.
This query provides a list of all donors, and as I want to produce a statement for an individual donor, I need to select just his details.
Ocan do this as a parameter within the q2_charity_statement using QBE, but I wished to do this from a form which has already got a combo1 on it with the [full_name] parameter.
I was thus hoping to run, in VBA, the q2_charity_statement SQL using WHERE [full_name] = Combo1 to provide only the required donor's details.
A report (statement) would then be run on this output.
I am going for a cup of tea and a quiet think about where to go from here.
Thank you for the pointer. ... it is much appreciated.
Hopefully, at the end of this, once the user has brought up the form with the donors details on it he will be able to click on Command2 ("Produce Statement"), and off it will whirl into a preview of the desired report.
Go to the top of the page
 
John Spencer
post Nov 12 2010, 09:54 AM
Post#4


UtterAccess VIP
Posts: 2,550
Joined: 24-March 08
From: Columbia, Maryland


Assumption: the report is based on a saved query that is not filtered by name
The button's click event might look like
Private Sub NameOfButton_Click()
Dim strWhere as String
'Build a where clause without the word where
'any field referenced in the clause must exist in the
'record source of the report
strWhere = "Full_Name =""" & Me.ComboBoxName & """"
DoCmd.OpenReport "NameOfReport", acViewNormal,, strWhere
End Sub
Go to the top of the page
 
MrChips
post Nov 12 2010, 10:42 AM
Post#5



Posts: 387
Joined: 3-July 05
From: Tonbridge, Kent, England


My word .... that is so elegant.
Is you probably expected, it worked first time.
Oneed to spend more time looking at some of these constructs.
I tend to rush into something far too quickly .... but with yours and guys like you, I am slowly learning.
Your help is much appreciated.
I now have to work out those """'s.
regards, and thanks again ....... so elegant ..... and I do like elegance.
Go to the top of the page
 
John Spencer
post Nov 12 2010, 03:24 PM
Post#6


UtterAccess VIP
Posts: 2,550
Joined: 24-March 08
From: Columbia, Maryland


The multiple quotes are there because to embed a quote in a string variable you need two quotes in a row. You could write it as
trWhere = "Full_Name =" & Chr(34) & Me.ComboBoxName & Chr(34)
Chr(34) is the quote Character.
With either expression strWhere contains
Full_Name = "Whatever is selected in the combobox"
Go to the top of the page
 


Custom Search
RSSSearch   Top   Lo-Fi    22nd April 2018 - 05:49 AM