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]

Hi Cintury,

Is this an Access db problem? If so, it should probably be in the Access forum instead of the VB6 forum (especially since there are numerous differences between the VBA that Access uses and full-bore VB)

Assuming that this IS Access:

One work-around that is probably simpler to implement with your situation is to create a 'test form' with your second date field in it, that you open as Hidden in the AfterUpdate event of your Date control (combo/text whatever box). The code for opening a hidden form looks like this:

DoCmd.OpenForm "tfrmIng", , , , , acHidden

MAKE SURE to close this form with DoCmd.Close once you are done using it, as open hidden forms can make for headaches as you develop further (said from experience :-)

Then the solution is as simple as comparing the two date fields and popping up your message box when appropriate.

As an aside, you should probably rename your 'Date' field, as Date is a reserved word in MS Access/VBA/VB. You'll run into problems sooner or later using it as is...

To research recordsets more, do a search on this site using the .recordset keyword.

Regards,
Scott

[Scott Price]

Here is a link to a thread discussing how to highlight a specific record in a listbox using recordset/.findfirst

Not exactly what you're looking for, but may help you understand recordsets a bit more...

http://www.thescripts.com/forum/thread689835.html

Regards,
Scott

[Killer42]

Ignore me, I'm just registering an interest in this thread so I'll be notified of updates.

[Cintury]

Hi Cintury,

Is this an Access db problem? If so, it should probably be in the Access forum instead of the VB6 forum (especially since there are numerous differences between the VBA that Access uses and full-bore VB)

Assuming that this IS Access:

One work-around that is probably simpler to implement with your situation is to create a 'test form' with your second date field in it, that you open as Hidden in the AfterUpdate event of your Date control (combo/text whatever box). The code for opening a hidden form looks like this:

DoCmd.OpenForm "tfrmIng", , , , , acHidden

MAKE SURE to close this form with DoCmd.Close once you are done using it, as open hidden forms can make for headaches as you develop further (said from experience :-)

Then the solution is as simple as comparing the two date fields and popping up your message box when appropriate.

As an aside, you should probably rename your 'Date' field, as Date is a reserved word in MS Access/VBA/VB. You'll run into problems sooner or later using it as is...

To research recordsets more, do a search on this site using the .recordset keyword.

Regards,
Scott

Hi Scott,

Thanks for the help. This IS access btw (i forgot to mention it i guess). There are a few questions I have about your solution.

The form that needs to be checked is a single form and I understand how I would check that against another single form, but the form that I need opened which would be hidden is a main form with a sub-form that needs to be searched. The main form to be searched is sorted by a store ID while the sub-form that needs to be searched is sorted by receipt ID. There is no store ID field in the form that I would be searching from. In order to get to the correct subform I'd have to go thru some complex linkages to get to get the matching record where as I thought that searching the table against the form via recordset would be much simpler.

Anyone with thoughts on this please comment.


Thank you!

[Scott Price]

I'm not sure if you are understanding the solution I was referring to, so I'll explain the idea a little more. If it doesn't match your situation, go ahead and disregard!

When you enter a date to be matched, you need to match it against something. The solution you were attempting at first was from one main form to another form's subform... Fine. What I was suggesting you do, is instead of comparing the two forms, create a 3rd form that has the date (and whatever else field you wish to match on) in it. This 3rd form is the one you open as hidden. Then the comparison is quite simple, and the resulting actions are also more simpler. I.e., Compare Form1.CheckDate against hidden Form2.Validatio nDate then DoAction to Form3.SubForm.R eceiptID

As for recordsets, I would say you need to use DAO recordsets since ADO means ActiveX Data Objects (the supposedly new and improved and M$ preferred advancement to DAO, which means Data Access Object, and refers to the original Jet container for all database objects in M$ Access, ActiveX references are notoriously difficult because of version incompatibility , while DAO still seems to work without any problems).

You should also form your query outside of VBA and store it as a saved query. Then refer to it in your recordset definition (the reasons are explained here in this link: Stored Query vs SQL Statement.

Go ahead and look through the articles in the Articles-Access section. You will find some very good information there regarding recordsets, DAO vs ADO, etc.

If you continue having problems, or there is something in the articles you don't understand, post back here and we'll do our best to help you!

Regards,
Scott

p.s. Killer, would you move this across to the Access forum, please?

[Cintury]

I'm not sure if you are understanding the solution I was referring to, so I'll explain the idea a little more. If it doesn't match your situation, go ahead and disregard!

When you enter a date to be matched, you need to match it against something. The solution you were attempting at first was from one main form to another form's subform... Fine. What I was suggesting you do, is instead of comparing the two forms, create a 3rd form that has the date (and whatever else field you wish to match on) in it. This 3rd form is the one you open as hidden. Then the comparison is quite simple, and the resulting actions are also more simpler. I.e., Compare Form1.CheckDate against hidden Form2.Validatio nDate then DoAction to Form3.SubForm.R eceiptID

Ok I've changed my approach as you've suggested to use the hidden form. The problem I'm having now is that I can't access the hidden form. This is the error I get:

[ERROR]
Compile Error: Variable not Defined
[/ERROR]

This is the code with an arrow (->)next to the stopping point.

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

Thank you in advance for the advice

[Scott Price]

Instead of this:

DoCmd.OpenForm "frmFoodDonatio nbyDate", , , , , acHidden
-> frmFoodDonation byDate.Filter = "[FoodDonationsID]" & tempFieldID
frmFoodDonation byDate.FilterOn = True

Try this:

DoCmd.OpenForm "frmFoodDonationbyDate", , ,"[FoodDonationsID] =" & tempFieldID, , acHidden

Also in your lines 11 & 13 you'll need to change txtDistribution Date.Value to: Me!txtDistribut ionDate (no need to add the .Value, as that is the default setting for referring to a control)

Let me know how it goes!

Regards,
Scott

[Cintury]

Instead of this:


Try this:

DoCmd.OpenForm "frmFoodDonationbyDate", , ,"[FoodDonationsID] =" & tempFieldID, , acHidden

Also in your lines 11 & 13 you'll need to change txtDistribution Date.Value to: Me!txtDistribut ionDate (no need to add the .Value, as that is the default setting for referring to a control)

Let me know how it goes!

Regards,
Scott

Ok these changes have decreased the code size substantially and it looks like the solution is within sight, but I'm still receiving an error. I get a runtime error 2501 Open Form was cancelled. Now I know for a fact that receipt ID is correct because it is drawn from the tblFoodDonation s using a combobox so it has to find the Id in the form query. I just don't know why it's cancelling the form open.

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

[Scott Price]

Ok these changes have decreased the code size substantially and it looks like the solution is within sight, but I'm still receiving an error. I get a runtime error 2501 Open Form was cancelled. Now I know for a fact that receipt ID is correct because it is drawn from the tblFoodDonation s using a combobox so it has to find the Id in the form query. I just don't know why it's cancelling the form open.

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

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