Querying a recordset with VBA Variables

[Cintury]

Ok the problem originally started with me wanting to compare the dates entered on one form with the dates entered on a different form's subform (I hope that wasn't too confusing). Thru numerous trials and error, I discovered that it was not possible to do it this way since 1) the other subform had to be open and 2) the main form of the searchee subform had to be on the correct record already (headache!).

Then I stumbled onto recordsets. I think I've found the solution. However I have a few concerns.

• Do I use a DAO recordset or an ADO recordset (just trying to access a table and compare its values to some VB variables)
• Is my syntax correct for the query
• Why is it coming back with an empty recordset?

Private Sub txtDistributionDate_LostFocus()
Dim rs As DAO.Recordset
Dim DB As DAO.Database
Dim tempDate As Date
Dim tempFieldID, strSQL As String
 
 Set DB = CurrentDb()
 
  Me.cboFoodDonationsID.SetFocus
  tempFieldID = Me.cboFoodDonationsID.Text
  MsgBox tempFieldID, vbOKOnly
 
 
  Set rs = DB.OpenRecordset("SELECT Date FROM tblFoodDonations WHERE [tblFoodDonations]![FoodDonationsID] ='" & tempFieldID & " ' ")
    rs.MoveFirst
 
    Do While Not rs.EOF
    rs.MoveNext
'TEST to see the current seached record
    MsgBox rs("FoodDonationsID").Value + rs("Date").Value, vbOKOnly
 
    Loop
    tempDate = rs("Date").Value
 
 
 
  If Me.txtDistributionDate > tempDate Then
  MsgBox "Please enter a date that is after the product was Donated!", vbExclamation, "Date Check"
 
 
 
  Me.txtDistributionDate.SetFocus
 
  End If
 
  rs.Close
 
End Sub
 

Thank you in advanced for your help!

[Scott Price]

Thanks for sticking it out with me up to this point Scott, I really and truly appreciate it. As you can tell I haven't worked to heavily in the forms and controls side of Access, I'm usually tinkering with the raw tables and queries.

Anyway the hidden form is based on a simple query to the foodDonations table.

SELECT tblFoodDonations.FoodDonationsID, tblFoodDonations.Date
FROM tblFoodDonations;
 

No worries, mate :-)

Looks good so far, just go ahead and add the WHERE clause to your query. You can do this easier in design view rather than SQL view. Just add the line referring to your main form in the criteria grid of your design view window. i.e. Forms![YourFormName].[YourControlName]

Run the query to make sure it works, then we can return to the code to open the hidden form!

Regards,
Scott

[Cintury]

No worries, mate :-)

Looks good so far, just go ahead and add the WHERE clause to your query. You can do this easier in design view rather than SQL view. Just add the line referring to your main form in the criteria grid of your design view window. i.e. Forms![YourFormName].[YourControlName]

Run the query to make sure it works, then we can return to the code to open the hidden form!

Regards,
Scott

Code is changed and the query works when I input a value in the pop-up for Forms![frmAgencyDonati on].[cboFoodDonation sID]

SELECT tblFoodDonations.FoodDonationsID, tblFoodDonations.Date
FROM tblFoodDonations
WHERE (((tblFoodDonations.FoodDonationsID)=[Forms]![frmAgencyDonation]![cboFoodDonationsID]));
 

However I'm just realizing that I never set the hidden form's FoodDonationsID = to anything so the query won't work automatically until I do. (GIANT D'OH Moment)

DoCmd.OpenForm "frmFoodDonationbyDate", , , , , acHidden
 

So in the above statement I now need to set frmFoodDonation byDate.FoodDona tionsID = tempFieldID in order for the query to work. I just need to know how to set control criteria in that do cmd statement.

Thanks!

[Scott Price]

Now all you need to do is go into your form in design view. It should have only two text boxes, one named FoodDonationsID the other named DonationDate (or whatever). Make sure the text boxes are bound to the right fields from your query, and that should be all there is to it!

Because the query is only going to return the one record, there is no need to set any criteria in your DoCmd.OpenForm command. Once it opens, it's set with the data you need to compare against.


Regards,
Scott

[Cintury]

Now all you need to do is go into your form in design view. It should have only two text boxes, one named FoodDonationsID the other named DonationDate (or whatever). Make sure the text boxes are bound to the right fields from your query, and that should be all there is to it!

Because the query is only going to return the one record, there is no need to set any criteria in your DoCmd.OpenForm command. Once it opens, it's set with the data you need to compare against.


Regards,
Scott

Hmm ok as follows for the hidden form:

• Food DonationsID Control Source: [qryFoodDononati onDates]![FoodDonationsID]
  
• Date Control source: [qryFoodDononati onDates]![Date]

The problem is that at this line i'm still getting the object not set error:

If frmFoodDonationbyDate.txtDonDate > Me.txtDistributionDate Then
 

I've verified that the control name is correct so right now I'm at a lost as to what is causing it.

[Scott Price]

Try this:

If Forms!frmFoodDonationbyDate.txtDonDate > Me.txtDistributionDate Then

Instead of this:

If frmFoodDonationbyDate.txtDonDate > Me.txtDistributionDate Then

Regards,
Scott

[Cintury]

Try this:

If Forms!frmFoodDonationbyDate.txtDonDate > Me.txtDistributionDate Then

Instead of this:

If frmFoodDonationbyDate.txtDonDate > Me.txtDistributionDate Then

Regards,
Scott

Arrrg... Runtime error 2447 invalid use of the .(dot) or ! operator or invalid parenthesis.

[Scott Price]

Arrrg... Runtime error 2447 invalid use of the .(dot) or ! operator or invalid parenthesis.

Using the code I gave you gives you this error? What version of Access are you using? I use Access 2003, and everything I've given you is tested and workable in VBA 6.0... If you are using 2003, check your references to see if there are any marked as missing: in the VBA editor window, click the Tools menu, the very first menu item is References. Click it and scroll through to see if any show up as missing. If so, uncheck/check their check boxes, close the menu, Compile, Save, Compact/Repair and close the db. Restart your db and try the code again.

If you are using 2007 I'll have to call in someone else, since I have no idea what M$ has changed between the two versions of VBA.

Regards,
Scott

[Killer42]

Also, it might be helpful to know exactly what VBA pointed at in the code, when it reported the error.

[Cintury]

Also, it might be helpful to know exactly what VBA pointed at in the code, when it reported the error.

I'm running Access 2003 with the Office 2003 sp3 installed and VB6/VBA is the language. I checked the references and none turned up as missing. The error turned up on the very line that Scott gave me.

[Scott Price]

I'm running Access 2003 with the Office 2003 sp3 installed and VB6/VBA is the language. I checked the references and none turned up as missing. The error turned up on the very line that Scott gave me.

Hi Cintury,

In your references, make sure the DAO reference is version 3.6, also that the ActiveX reference is 2.1 (the ActiveX one shouldn't have anything to do with our problem, but check it anyway)

Also: Humor me on this one :-) Try going to this site and clicking on the Check for Office Upates button on the right hand side of the screen. http://office.microsoft.com/en-us/do...s/default.aspx

I'm coming up blank on any other reason for your error except that you've got some corrupted files/references in your Office installation, or that you have a corrupted database file. Check out this link for Allen Browne's recommendations on how to recover from a corrupted db file: http://allenbrowne.com/ser-47.html

The code I gave you, as I said, is tested and working on MS Access 2003 in one of my databases.

Regards,
Scott