Help with Displaying Recordset (1 Viewer)

[majette97]

Below is the code that I am using; I pick a date from a drop-down list, and am trying to display the first data record that corresponds to the date, but it is not working. What displays is the first record of the file.

The Me! section of the code is not displaying the correct data record.
Can someone HELP!!!

Private Sub WeekNo_Click()
Dim SQLstr As String
Dim rs As Object
SQLstr = "SELECT * FROM [Time Card Hours] INNER JOIN [Time Cards] "
SQLstr = SQLstr & "ON [Time Card Hours].TimeCardID = [Time Cards].TimeCardID "
SQLstr = SQLstr & "WHERE ((([Time Cards].DateEntered)=" & [WeekNo].Text & "));"
Set rs = CurrentDb.OpenRecordset(SQLstr)

Me![Time Cards SubForm].Form![ProjectID].Text = rs![ProjectID]
Me![Time Cards SubForm].Form![TaskName].Text = rs![TasksNo]
Me![Time Cards SubForm].Form![Monday] = rs![MON]
Me![Time Cards SubForm].Form![Tuesday] = rs![TUE]
Me![Time Cards SubForm].Form![Wednesday] = rs![WED]
Me![Time Cards SubForm].Form![Thursday] = rs![THU]
Me![Time Cards SubForm].Form![Friday] = rs![FRI]
Me![Time Cards SubForm].Form![Saturday] = rs![SAT]
Me![Time Cards SubForm].Form![Sunday] = rs![SUN]
Me![Time Cards SubForm].Form![Progress] = rs![Progress]
Me![Time Cards SubForm].Form![ProjectID].Requery
End Sub


THANKS!!!!!

 

[chrismcbride]

Your existing code seems to be trying to overwrite the current record with details mined from the Recordset. Assuming you want to navigate to an existing record, then you should use the RecordsetClone's Bookmark property to move you to the record.

You could add the Project Id as a new column to the combo box from which you are selecting the date. Hide the Id column using the ColumnWidth property. Then once the user has chosen a Date, you can add this code to the AfterUpdate of the Combo Box...

Me.RecordsetClone.FindFirst "[Product Id] = " & Me!cboDate.Column(1)
Me.Bookmark = Me.RecordsetClone.Bookmark

The only trick with this is that the Combo Box can not be bound to the Date field in the uderlying table.

HTH
Chris

 

[majette97]

Chris,

What are you referring to when you say ProjectID? Are you referring to the field that I am already using?
And what field are you referring to as
cboDate.Column(1)
Are you referring to my date field WeekNo ?

I already have the record by using this string of code: SQLstr = SQLstr & "WHERE ((([Time Cards].DateEntered)=" & [WeekNo].Text & "));"
Set rs = CurrentDb.OpenRecordset(SQLstr)

I really just need to know how to display the data; because the first record in the file is being displayed.

This code is not displaying the correct data record.
Me![Time Cards SubForm].Form![ProjectID].Text = rs![ProjectID]
Me![Time Cards SubForm].Form![TaskName].Text = rs![TasksNo]
Me![Time Cards SubForm].Form![Monday] = rs![MON]
Me![Time Cards SubForm].Form![Tuesday] = rs![TUE]
Me![Time Cards SubForm].Form![Wednesday] = rs![WED]
Me![Time Cards SubForm].Form![Thursday] = rs![THU]
Me![Time Cards SubForm].Form![Friday] = rs![FRI]
Me![Time Cards SubForm].Form![Saturday] = rs![SAT]
Me![Time Cards SubForm].Form![Sunday] = rs![SUN]
Me![Time Cards SubForm].Form![Progress] = rs![Progress]
Me![Time Cards SubForm].Form![ProjectID].Requery
End Sub

 

[majette97]

TO ALL:
CAN ANYONE HELP!!!

THANKS!

 

[charityg]

What Chris was trying to tell you is that you aren't diplaying the record. Your code is overwriting the field's data on the current record.

You'll need to use the bookmark property to "Go to" the corresponding record, but first you need to set the recordset as the recordsetclone of the form, and find the first record that matches your criteria.

dim rs as recordset 'not object
set rs=me.recordsetclone

rs.findfirst "[DateEntered]=" & me!WeekNo
me.bookmark=rs.bookmark


You should put this code in the afterupdate event of the combobox (drop down list)

 

[majette97]

charityg, Thanks for you response.
We tried that code and we are getting a 'Compile Error; Can't find project or library' on dim rs as recordset line of code. We are using MS Access 97. Do you have any idea what we need to do?

Thanks!

 

[charityg]

In the code window, tools -> references and make sure the Microsoft DAO 3.5 object library is checked.

 

[majette97]

Microsoft DAO 3.51 object Library is checked.

I used Object instead of Recordset and I don't get an error, but no data is displaying.

Do you have an idea on how to get this working?

THANKS!

 

[chrismcbride]

What version of Access are you using? I use A 97 and, as such, do not have to refer directly to the type of recordset that I want to create. By this I mean that...

Dim rst as Recordset
... gives me an ADO recordset by default.

I think( but am not sure) that you must explicitly declare your recordset types in A2K..

Dim rst as ADODB.Recordset

...
Additionally I have never found it neccessary to make a variable recordset equal to the form's recordsetclone property (although it would work fine).

One more thing would be that I would normally add the record id to any combo box field. This is the field that I call Project Id (I though that was what you were calling it as well). Once the record id is stored in the combo box, then searching for a record is easily done (this is the meaning of & Me!cboDate.Column(1) - cboDate is the combo box - the combo box has two columns - column 0 holds the date value and column 1 holds the record id).

By the way, people who reply to posts are not notified if there are follow up questions by the original poster and I find it hard to get to this board more than a few times a week. I will respond to an e-mail if you are still having trouble...but patience may be required.
HTH
Chris