SELECT FROM problems...

[fluidmind]

Hi there!

This should be an easy question. The billingperiods are stored in the table 'billingperiod' containing three columns: 'month', 'fromdate' and 'todate'.
After update of a textbox called "navn", I want the 'fromdate' of the month 'july' displayed in Tekst18.

This is my code:

Private Sub navn_AfterUpdate()

strSQL = "SELECT * FROM billingperiod WHERE month = 'july'"

Me.Tekst18 = fromdate

End Sub

But my SQL-sentence doesn't seem to run at all. I've tried writing DoCmd.RunSQL after the SQL-sentence. But that doesn't make it work... Is there another command I have to use instead???

// JR

 

[sckoobs]

Hi fluidmind,

You'll have to establish a database connection before you can return result sets from queries, like this:

Dim dbs as Database
Dim rst as Recordset

Set dbs = Currentdb

Set rst = dbs.Openrecordset("Your SQL Query Here")

Me.Tekst18 = rst("fromdate")

rst.Close

Typically, you'd only use DoCmd.RunSQL or dbs.Execute if you don't expect a result set back, but SELECT statements are normally done using Recordset objects and executed using the Database.Openrecordset method or Querydef objects.

 

[fluidmind]

Another problem...

Hi again!

Thank you so much for your reply... It really helped me understand the basic background-knowledge. I've been puzzling with it for a while now and I've encountered two problems:

1: I couldn't make it work without inserting 'DOA' in front of 'database' and 'recordset'. I don't know what it does but I saw another guy do it, so I tried it. And now I'm past the error that i got

2: The second problem has not been solved yet and believe me - I've tried. I think the code pretty much explains what I want to do. But it just won't work. If I write like 'august' or 'july' instead of [month] it works perfectly. But I want to get the value from a scroll-down menu called 'month' as I've tried.

What am I doing wrong???

//JR

Private Sub navn_AfterUpdate()

Dim dbs As DAO.database
Dim rst As DAO.Recordset

Set dbs = CurrentDb
Set rst = dbs.OpenRecordset("SELECT * FROM lønperioder WHERE navn = [month] ")

Me.Tekst18 = rst("fradato")
Me.Tekst20 = rst("tildato")

rst.Close

End Sub

 

[sckoobs]

Right, I'm assuming that you're storing the month in the lønperioder table as a string, i.e. of data type Text in the table design view. So what you want is to tell the database engine that you're providing a string by putting quotes around the value you are passing, like so:

Set rst = dbs.OpenRecordset("SELECT * FROM lønperioder WHERE navn = """ & [month] & """")

This is assuming that the month value is part of the recordset of a bound form, otherwise you can just use the value from your month combo box.

The way you have it just now will not work because you are passing [month] in as a litteral, i.e. part of the string, so the database is actually looking for records where the month = [month], using the query I've shown you above, the [month] variable is substituted via string concatenation for the value inside it.

 

[fluidmind]

Thanks so much

In my desperate search for a solution I tried quite a lot. Putting numerous "s, 's, &s, ()s and []s around the 'month'. But I never thought it would actually take 3 x "s, 1 x & and 1 x &s to get it right

But now it both works and I've improved my knowledge on the subject

Thank you so much!!!

// JR