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]

Three things I notice here:

Number one is that you have declared your tempFieldID as String. This should be Integer or Long because you are comparing it (presumably that is, unless you have made your ID fields alphanumeric) to a number field later on.

Number two is that you are missing a space between [FoodDonationsID] and the = sign.

Number three is you still need to correctly rename your txtDistribution Date field to Me.txtDistribut ionDate

Make these changes and try again. (btw I hope you are remembering to compile your changes, save your changes and recheck compile status during this process. If you don't, you likely won't notice the warning signs of corruption until it's almost too late!)

Regards,
Scott

Thanks for all your help so far Scott, I really really do appreciate it because this thing has been bugging me for a week and a half.

So I've fixed number 2 and 3 but the tempField is a string because although we would rather not some of our receipts are alphanumeric due to our printing company giving us rerun numbers of our paper receipts. This isn't caught until data entry time so we're forced to add a letter to the end of the FoodDonationsID on the receipt (both on paper and on screen for consistency).

Private Sub txtDistributionDate_AfterUpdate()
Dim tempFieldID As String
Dim frmFoodDonationbyDate As Form
 
Me.cboFoodDonationsID.SetFocus
tempFieldID = Me.cboFoodDonationsID.Value
 
(A)-> DoCmd.OpenForm "frmFoodDonationbyDate", , , "[frmFoodDonationbyDate]![FoodDonationsID] =" & tempFieldID, , acHidden
DoCmd.Requery
 
(B)->If [frmFoodDonationbyDate]![txtDonDate] > Me.txtDistributionDate Then
 MsgBox "Please enter a Delivery date that is after that of the date of Donation!", vbOKOnly, "Out of Range Date Exception"
 Me.txtDistributionDate.SetFocus
 End If
 
DoCmd.Close acForm, "frmFoodDonationbyDate"
End Sub
 

I'm no longer receiving an error at arrow A but there is a pop-up box that requests this member when the comparison runs: [frmFoodDonation byDate]![FoodDonationsID]

I'm only testing one receipt number = 56846 and I've found it on the query and on the form. I'm wondering if it is not being found because the form is hidden?

The issue at arrow B is that when I mouse over it in the VBA code I'm not seeing a value for it like I should. I of course see the correct value for txtDistribution Date.

[Scott Price]

Let's take care of A first, and then see if B still is a problem.

In your DoCmd.OpenForm statement, you have already referred to the form you want to open, so later in your Where clause, you don't need to refer to it again... It's sufficient to simply point to the field you are comparing. I.e. instead of "[frmFoodDonation byDate]![FoodDonationsID] =", it's enough to just put in: "[FoodDonationsID] =" &...

If both fields are string values, then you shouldn't have any trouble comparing them the way you have it set up. The trouble starts if you try to compare a string field with a number field.

One quick question that occurs: is the ReceiptID field the primary key of your table? If so, have you taken steps to make sure that it is, in fact, unique (i.e. there can never be duplicates in this field?)

You will end up with potentially inaccurate data if your answer to question two is no :-) What can happen is that if you are telling the db to open this hidden test form looking for a particular ReceiptID, and there are multiple instances of it, you will unknowingly be testing your date against what is possibly the wrong ReceiptID/Date combination because the test date will be the date entered for the FIRST record that is returned. This is because the other records will be on the form, but in single form view you can only see one record at a time...

Regards,
Scott

[Cintury]

Oh man sorry for potentially confusing the issue Scott. The receipt ID is the FoodDonationsID I was just referring to it with a generic term. Anyway the FoodDonationsID is alphanumeric as is the tempFieldID.

The table from which the query for the hidden form draws data (tblFoodDonatio ns) has the FoodDonationID as the unique identifier so there are NO duplicates.

That was the issue before. When I referred to it as just [FoodDonationsID] I received a runtime error 2501. But when I put in [frmFoodDonation byDate]![FoodDonationsID] I did not receive this error, I just did not receive a value for the txtDonDate.

[Scott Price]

Oh man sorry for potentially confusing the issue Scott. The receipt ID is the FoodDonationsID I was just referring to it with a generic term. Anyway the FoodDonationsID is alphanumeric as is the tempFieldID.

The table from which the query for the hidden form draws data (tblFoodDonatio ns) has the FoodDonationID as the unique identifier so there are NO duplicates.

That was the issue before. When I referred to it as just [FoodDonationsID] I received a runtime error 2501. But when I put in [frmFoodDonation byDate]![FoodDonationsID] I did not receive this error, I just did not receive a value for the txtDonDate.

No problem! I wasn't too confused, anyway :-) So is it working now?

Just to clarify, in your DoCmd statement to open the form, you need to refer to the Form control name, not the Table field name: i.e. txtFoodDonation sID > FoodDonationsID .

Regards,
Scott

[Scott Price]

Just went back and looked, and I see you were talking about subforms earlier!

Perchance is the txtDistribution Date control that you are referring to part of the subform?

If so, you need to refer to it by it's full name: i.e. Forms![YourMainFormNam e]![YourSubFormName].[ControlNameOnSu bForm]

If, also your cboFoodDonation sID is part of the same subform, it also needs to be referred to by it's full name, as described above.
See the following changes I've made to your code.

      Private Sub txtDistributionDate_AfterUpdate()
      Dim tempFieldID As String
      Dim frmFoodDonationbyDate As Form
 
      Me.cboFoodDonationsID.SetFocus
      tempFieldID = Me.cboFoodDonationsID.Value
 
      (A)-> DoCmd.OpenForm "frmFoodDonationbyDate", , , "[FoodDonationsID] =" & tempFieldID, , acHidden
      DoCmd.Requery
 
      (B)->If [frmFoodDonationbyDate]![txtDonDate] > Forms![YourMainFormName]![YourSubformName].txtDistributionDate Then
       MsgBox "Please enter a Delivery date that is after that of the date of Donation!", vbOKOnly, "Out of Range Date Exception"
       Me.txtDistributionDate.SetFocus
       End If
 
      DoCmd.Close acForm, "frmFoodDonationbyDate"
      End Sub

Regards,
Scott

[Cintury]

No problem! I wasn't too confused, anyway :-) So is it working now?

Just to clarify, in your DoCmd statement to open the form, you need to refer to the Form control name, not the Table field name: i.e. txtFoodDonation sID > FoodDonationsID .

Regards,
Scott

No it's not working yet unfortunately. To clarify things further when I created the new third hidden form, I made it a (tabular?) form I believe, and the control name is the same name as the control source. Name: FoodDonationsID ; Control Source: FoodDonationsID .

I'm not sure if this frmAgencyDonati on is a subform or not because as it stands data is being passed thru the code as it is. When I mouse over it in debug VBA code I do see the correct data. However when I mouse over the code for the hidden form nothing displays.

[Scott Price]

No it's not working yet unfortunately. To clarify things further when I created the new third hidden form, I made it a (tabular?) form I believe, and the control name is the same name as the control source. Name: FoodDonationsID ; Control Source: FoodDonationsID .

I'm not sure if this frmAgencyDonati on is a subform or not because as it stands data is being passed thru the code as it is. When I mouse over it in debug VBA code I do see the correct data. However when I mouse over the code for the hidden form nothing displays.

Go into your hidden form in design view and change it to single form view.

Once in design view, double click on the little square on the upper left of the design view window, which brings up the Properties for your form. A few lines down on the All tab is the setting to change to Single form view.

If the data is being passed from your frmAgencyDonati on, then it isn't a subform :-)

Regards,
Scott

[Cintury]

Go into your hidden form in design view and change it to single form view.

Once in design view, double click on the little square on the upper left of the design view window, which brings up the Properties for your form. A few lines down on the All tab is the setting to change to Single form view.

If the data is being passed from your frmAgencyDonati on, then it isn't a subform :-)

Regards,
Scott

Ok I've finally managed to get a different error. Maybe this is where the ADO vs. DAO stuff comes into play (code changes will be underlined). The hidden form is in single form view.

[ERROR]
Run-Time error: 429
Active X object cannot be created
[/ERROR]

( -> ) will refer to error line.

Private Sub txtDistributionDate_AfterUpdate()
Dim tempFieldID As String
Dim frmFoodDonationbyDate As New Form
 
Me.cboFoodDonationsID.SetFocus
tempFieldID = Me.cboFoodDonationsID.Value
 
-> DoCmd.OpenForm "frmFoodDonationbyDate", , , [frmFoodDonationbyDate]![FoodDonationsID] & "=" & tempFieldID, , acHidden
DoCmd.Requery
 
If [frmFoodDonationbyDate]![txtDonDate] > Me.txtDistributionDate Then
 MsgBox "Please enter a Delivery date that is after that of the date of Donation!", vbOKOnly, "Out of Range Date Exception"
 Me.txtDistributionDate.SetFocus
 End If
 
DoCmd.Close acForm, "frmFoodDonationbyDate"
End Sub
 

I removed the NEW from in front of Form at the top and now I'm just receiving the run-time error 91: Object variable or with not set. It occurs at the arrow still

[Scott Price]

If the form already exists there is absolutely no reason to dim it as a new form!

The reason you are getting the error you are getting now is that you've tried to recreate a form that already exists!

To backup a little, let's assume that you have based your hidden form on a query. The query looks a little like this:

SELECT tblFoodDonations.FoodDonationsID, tblFoodDonations.DonationDate FROM tblFoodDonations WHERE tblFoodDonations.DonationsID = Forms!frmFoodDonations.FoodDonationsID

Now you can do away with the where clause in your DoCmd.OpenForm line. It will then look something like this:

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

It looks like you've been making changes unnecessarily when one or another thing hasn't worked! It's good to try different things in most cases, but unfortunately you've only been managing to introduce other opportunities for error :-( I commend you for working at this, and I appreciate that you're willing to try different approaches. Don't give up yet!

There is no reason to change the DoCmd.OpenForm syntax from what I have posted... The syntax isn't the problem at this point.

Let me know what you have based your hidden form on, and let's go from there.

Regards,
Scott

[Cintury]

...
It looks like you've been making changes unnecessarily when one or another thing hasn't worked! It's good to try different things in most cases, but unfortunately you've only been managing to introduce other opportunities for error :-( I commend you for working at this, and I appreciate that you're willing to try different approaches. Don't give up yet!

There is no reason to change the DoCmd.OpenForm syntax from what I have posted... The syntax isn't the problem at this point.

Let me know what you have based your hidden form on, and let's go from there.

Regards,
Scott

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;