SQL as row source for list box (Access97)

Topic

New Reply

(Edited by Francois on 19-Jan-05 19:04. add listbox comment)

What if you use :
sSQL = “Insert Into [tbl_Date] Select [tbl_500 – Data].[Pay Date] FROM [tbl_500 – Data]”

If lst_Cutoff_Dates is a listbox you can’t set his rowsource to a Insert SQL statement. You have to use a Select statement

Reply | Quote

Replies

Oh, allright, I’ve tried
sSQL = “Select [tbl_500 – Data].[Pay Date] FROM [tbl_500 – Data]” with the same “success” LOL
Thanks

Reply | Quote

Ok, when I am running it as a Module – table gets populated.
When I run it on Form cmd Click event I am still getting that error.

Public Function cmd_Run_Click()
Dim sSQL As String
Dim dbR As DAO.Database

Set dbR = CurrentDb()
DoCmd.SetWarnings True

sSQL = “Insert into Data_table SELECT [tbl_500 – Data].[Pay Date] FROM [tbl_500 – Data];”
dbR.Execute sSQL
lst_Cutoff_Dates.RowSource = sSQL

End Function

Reply | Quote

You are still trying to use a Insert into as rowsource for your listbox.
What do you exactly want ?
Populate the table Data_table with your pay dates in the tbl_500-Data ?
Then you haven’t to insert them into a table.
Just set the rowsource of the listbox to :
SELECT [tbl_500 – Data].[Pay Date] FROM [tbl_500 – Data]

Reply | Quote

I am getting error that I can only run Action query which is Select Query not.
This is why I am changing it to Append Query.

I want this code to run on Form event cmd_Click and it does not !
But it does run when written as a Module.
That’s it…

Reply | Quote

OK, let me ask it in another way :
What is the purpose of this line:
lst_Cutoff_Dates.RowSource = sSQL
What are you trying to achieve with this line ?

Reply | Quote

There is a Form with a ListBox
ListBox contains Dates to Select
When Date Selected in ListBox – Report will be run for this particular Date

Thanks

Reply | Quote

If with the line
lst_Cutoff_Dates.RowSource = sSQL
you want to populate the listbox try this code :

Public Function cmd_Run_Click()

lst_Cutoff_Dates.RowSource = “SELECT [tbl_500 – Data].[Pay Date] FROM [tbl_500 – Data];”
lst_Cutoff_Dates.Requery

End Function

Reply | Quote

I was just trying to run simple Update

Private Sub txt_From_AfterUpdate(Cancel As Integer)
ReportEndDate = CDate(Me!txt_From)
MsgBox “edUpdated”

End Sub

and i’ve got the same error message. Could that be my references are off or something?
Once HansV told me to use ADO.database instead of Db.Database and it fixed the issue. I suspect the same thing is going on now. Thanks

Reply | Quote

Hans would never had told you to use ADO.database, since there is no such thing, although he might very well have told you to use ADODB.Recordset for something. Furthermore, the AfterUpdate event has no cancel argument. Invalid code is always going to give you error messages, and they won’t be fixed by references.

You cannot use an action query as a rowsource for anything, if for no other reason than the fact that action queries do not return rows. Are you trying to run an update query or are you trying to do something else. Earlier in this thread you indicated [indent]

---

When Date Selected in ListBox – Report will be run for this particular Date

---

[/indent]. Instead of asking why the code doesn’t work, try explaining what you want it to do, in detail. Then someone can help you.

Reply | Quote

No need to shout, Charlotte, people do make mistakes and HansV told me to use DAO.Database ofcourse!

Reply | Quote

Francois,
I used Update example just to see if any simple code will work within my Form and it does not work.
As I said code do execute when run as a Module and do give an error when Form used.
Thanks

Reply | Quote

If you want you can attach a stripped version of your db and I’ll have a look at it tomorrow. See HansV‘s post 401925 to get it under the 100K limit.

Reply | Quote

Thanks, Francois

Reply | Quote

Here we go with Attachment and please, chack references, THANKS

Reply | Quote

Here is a revised database that does what I think you were trying to do. Some things aren’t there because they didn’t appear to serve any purpose in this demo.

Reply | Quote

Charlotte, I am affraid to jinks it but it is a miracle! No error! I have to test it some more, but it is the miracle! You’ve made my day!
May I ask – is there particular something that I do that my codes are bad?

Thanks and have a good day

Reply | Quote

Well, to say it delicately, there was not much that could work.
Wrong references, code that means nothing, code in the wrong place, …
Don’t take it wrong, but I urgently advise you to buy a good beginners book about Access VBA if you want to go on with Access VBA. Do a search on “beginning vba book” in this forum and try to find any that is still available for Access 97.

Reply | Quote

I will only argue because I want to understand, not because i object.
My code i posted at the beginning worked for me sometimes ago. (Here I am working with Access that crashes on me every 30 min and no way to fix it).

So my code was
Private Sub cmd_Run_Click()
Dim sSQL As String
Dim dbR As DAO.Database

Set dbR = CurrentDb()
DoCmd.SetWarnings False

sSQL = “Insert Into [tbl_Date] Select Distinct [Pay Date] FROM [tbl_500 – Data]”
DoCmd.RunSQL sSQL
lst_Cutoff_Dates.RowSource = sSQL

End Sub

Can you please, analyze for me? Why error as I described it took place?

What Charlotte did for me was just an ideal solution and her code is totally different and great but whay mine didn’t work? It is so simple and should of worked… THANKS

Reply | Quote

First of all, this code should be in the form module and not in a separate module.

Dim sSQL As String
Dim dbR As DAO.Database
Set dbR = CurrentDb()
DoCmd.SetWarnings False
sSQL = “Insert Into [tbl_Date] Select Distinct [Pay Date] FROM [tbl_500 – Data]”
DoCmd.RunSQL sSQL
This code takes all the Pay Date and put them in a table tbl_Date. Why you do this is for us a mystery and has no sense.

lst_Cutoff_Dates.RowSource = sSQL
In this line, like I say it a few times before put a Insert sql statement in the rowsource of your listbox. A rowsource of a listbox can only contain a Select statement.

Reply | Quote

This code WAS in a form module – didn’t work, so i transfered it to Modules just to test and it worked!!!
I think it is just some unfortunate event.

THANKS I am happy now…

Reply | Quote

No, it didn’t work, it just didn’t throw an error. There is a big difference. If you had compiled the code with Option Explicit in the modules, you would have received an error in the module as well. One major oversight was to not have Option Explicit turned on in all your code modules. Without that, Access can’t know whether you are referring to a variable created on the fly or a control or other object, so it guesses. In this case, it guesses wrong and assumes the unrecognized names are variables. If you use Option Explicit, you are forced to declare variables before you use them. That is part of what makes debugging work.

I concur with Francois suggestion. You need a book on beginning VBA for Access and you need to start at the very beginning and learn the basics of writing code and testing it. In the database you posted, the code behind the form didn’t even match the names of the controls, which means they would not execute, no matter what. You also had a DAO 3.6 reference set, which is wrong. Access 97 uses DAO 3.51. The 3.6 DAO reference is for Access 2000 and later.

Programming is a process of figuring out what you want to accomplish, such as, select a date on a form and run a report to show information for that date. Once you know what you want to achieve, you figure out how you might do it and then select the way that makes the most sense. In your code, you were trying to set the rowsource of the listbox to a SQL statement that doesn’t return rows, which is impossible. In fact, it makes no sense to try and change the rowsource of the listbox at all, which is why I didn’t do it that way. Nor could I see a reason to run that update query, since it didn’t seem to accomplish anything except copying data from one table to another. Since the report used a crosstab query based on the first table, that operation has nothing to do with printing the report. I left it in but ignored it for purposes of running the report.

Reply | Quote

Now you’ve lost me !
You say that you want to run an Update, using an Insert statement where you should use a select statement.

For the references, in the VBE editor, select Tools, References and see if you have some that start with MISSING…
If so uncheck them and try to run the code .

Reply | Quote

Subject edited by HansV to be more informative than ‘Hi!’

Hi!
Trying to execute code

Private Sub cmd_Run_Click()
Dim sSQL As String
Dim dbR As DAO.Database

Set dbR = CurrentDb()
DoCmd.SetWarnings False

sSQL = “Insert Into [tbl_Date] Select Distinct [Pay Date] FROM [tbl_500 – Data]”
DoCmd.RunSQL sSQL
lst_Cutoff_Dates.RowSource = sSQL

End Sub

Getting error
The expression on click you entered … produces … error
The expression may not result in the name of macro
There may have been an error evaluating the function…

Do you see anything wrong with my code? Thanks

Reply | Quote