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!

[Cintury]

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

WoW that took a looooong time to figure out. It turns out that VBA did not like Forms! and wanted Form!. But now I'm getting the error 2465: can't find the field frmFoodDonation byDate referred to in your expression.

Do you think this is because it is hidden?

DoCmd.OpenForm "frmFoodDonationbyDate", , , , , acHidden
DoCmd.Requery
 
-> If Form!frmFoodDonationbyDate.txtDonDate > Me.txtDistributionDate Then
 

BTW the DAO and ActiveX are up to date, the only update not installed was VS 2005 sp1 and I just installed and rebooted.

[Cintury]

On a side-note I've just noticed that when I'm in VBA in the Project window to my left, my form does not show up. Does this mean something?

[Scott Price]

WoW that took a looooong time to figure out. It turns out that VBA did not like Forms! and wanted Form!. But now I'm getting the error 2465: can't find the field frmFoodDonation byDate referred to in your expression.

Do you think this is because it is hidden?

DoCmd.OpenForm "frmFoodDonationbyDate", , , , , acHidden
DoCmd.Requery
 
-> If Form!frmFoodDonationbyDate.txtDonDate > Me.txtDistributionDate Then
 

BTW the DAO and ActiveX are up to date, the only update not installed was VS 2005 sp1 and I just installed and rebooted.

This is a direct quote from the VBA help file regarding how to refer to forms:

A Form object refers to a particular Microsoft Access form.

Using the Form Object
A Form object is a member of the Forms collection, which is a collection of all currently open forms. Within the Forms collection, individual forms are indexed beginning with zero. You can refer to an individual Form object in the Forms collection either by referring to the form by name, or by referring to its index within the collection. If you want to refer to a specific form in the Forms collection, it's better to refer to the form by name because a form's collection index may change. If the form name includes a space, the name must be surrounded by brackets ([ ]).

Syntax Example
Forms!formname Forms!OrderForm
Forms![form name] Forms![Order Form]
Forms("formname ") Forms("OrderFor m")
Forms(index) Forms(0)


Each Form object has a Controls collection, which contains all controls on the form. You can refer to a control on a form either by implicitly or explicitly referring to the Controls collection. Your code will be faster if you refer to the Controls collection implicitly. The following examples show two of the ways you might refer to a control named NewData on the form called OrderForm:

' Implicit reference.
Forms!OrderForm !NewData

' Explicit reference.
Forms!OrderForm .Controls!NewDa ta

The next two examples show how you might refer to a control named NewData on a subform ctlSubForm contained in the form called OrderForm:

Forms!OrderForm .ctlSubForm.For m!Controls.NewD ata

Forms!OrderForm .ctlSubForm!New Data

As you can see, it includes examples that quite clearly indicate that the correct syntax in VBA 6.0 is Forms!, not Form!

If Form! is working for you (which as you say, it's NOT), then happy trails :-)

However, since the error message you mention is indicating that because you are referring to it by Form!... it thinks the frmDonationByDa te is a FIELD in a form name Form, not a form... And, correctly, it can't find any such field on any such form.

Check very closely to make sure that you are referring to the names of the forms and particularly the names of the text boxes/controls correctly! a small misspelling will result in the db not being able to find the form you want.

If you still can't figure it out, please email a copy of the db, I'll take a look and see what I can do for you (send me a PM if you want to go this route, and I'll enable my email address so you can send the db to me).

And no, it's not because the form is hidden that it can't be found! Hidden forms are only invisible to the users of the database, not to the database itself.

Regards,
Scott

[Scott Price]

On a side-note I've just noticed that when I'm in VBA in the Project window to my left, my form does not show up. Does this mean something?

Not really. The hidden form will only show in your VBA editor window if you have opened a module for it (by clicking on the ellipsis next to an event and choosing Code Builder.

This doesn't mean that it's not accessible to the database, just that you haven't entered any VBA code for the particular form

Regards,
Scott