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?

Expand|Select|Wrap|Line Numbers

 
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:

Expand|Select|Wrap|Line Numbers

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

Quote:

Originally Posted by 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:

Expand|Select|Wrap|Line Numbers

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.ValidationDate then DoAction to Form3.SubForm.ReceiptID

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

Quote:

Originally Posted by 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.ValidationDate then DoAction to Form3.SubForm.ReceiptID

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.

Expand|Select|Wrap|Line Numbers

 
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:

Quote:

DoCmd.OpenForm "frmFoodDonationbyDate", , , , , acHidden
-> frmFoodDonationbyDate.Filter = "[FoodDonationsID]" & tempFieldID
frmFoodDonationbyDate.FilterOn = True

Try this:

Expand|Select|Wrap|Line Numbers

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

Also in your lines 11 & 13 you'll need to change txtDistributionDate.Value to: Me!txtDistributionDate (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

Quote:

Originally Posted by Scott Price

Instead of this:

Try this:

Expand|Select|Wrap|Line Numbers

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

Also in your lines 11 & 13 you'll need to change txtDistributionDate.Value to: Me!txtDistributionDate (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 tblFoodDonations 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.

Expand|Select|Wrap|Line Numbers

 
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

Quote:

Originally Posted by Cintury

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 tblFoodDonations 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.

Expand|Select|Wrap|Line Numbers

 
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 txtDistributionDate field to Me.txtDistributionDate

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

Cintury

Quote:

Originally Posted by Scott Price

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 txtDistributionDate field to Me.txtDistributionDate

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).

Expand|Select|Wrap|Line Numbers

 
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: [frmFoodDonationbyDate]![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 txtDistributionDate.

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 "[frmFoodDonationbyDate]![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 (tblFoodDonations) 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 [frmFoodDonationbyDate]![FoodDonationsID] I did not receive this error, I just did not receive a value for the txtDonDate.

Scott Price

Quote:

Originally Posted by 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 (tblFoodDonations) 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 [frmFoodDonationbyDate]![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. txtFoodDonationsID > FoodDonationsID.

Regards,
Scott

Scott Price

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

Perchance is the txtDistributionDate 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![YourMainFormName]![YourSubFormName].[ControlNameOnSubForm]

If, also your cboFoodDonationsID 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.

Expand|Select|Wrap|Line Numbers

 
      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

Quote:

Originally Posted by Scott Price

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. txtFoodDonationsID > 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 frmAgencyDonation 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

Quote:

Originally Posted by Cintury

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 frmAgencyDonation 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 frmAgencyDonation, then it isn't a subform :-)

Regards,
Scott

Cintury

Quote:

Originally Posted by Scott Price

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 frmAgencyDonation, 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.

Expand|Select|Wrap|Line Numbers

 
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:

Expand|Select|Wrap|Line Numbers

 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:

Expand|Select|Wrap|Line Numbers

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

Quote:

...
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.

Expand|Select|Wrap|Line Numbers

 
SELECT tblFoodDonations.FoodDonationsID, tblFoodDonations.Date

FROM tblFoodDonations;

Scott Price

Quote:

Originally Posted by Cintury

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.

Expand|Select|Wrap|Line Numbers

 
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

Quote:

Originally Posted by Scott Price

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![frmAgencyDonation].[cboFoodDonationsID]

Expand|Select|Wrap|Line Numbers

 
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)

Expand|Select|Wrap|Line Numbers

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

So in the above statement I now need to set frmFoodDonationbyDate.FoodDonationsID = 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

Quote:

Originally Posted by 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

Hmm ok as follows for the hidden form:

Food DonationsID Control Source: [qryFoodDononationDates]![FoodDonationsID]
Date Control source: [qryFoodDononationDates]![Date]

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

Expand|Select|Wrap|Line Numbers

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:

Expand|Select|Wrap|Line Numbers

If Forms!frmFoodDonationbyDate.txtDonDate > Me.txtDistributionDate Then

Instead of this:

Expand|Select|Wrap|Line Numbers

If frmFoodDonationbyDate.txtDonDate > Me.txtDistributionDate Then

Regards,
Scott

Cintury

Quote:

Originally Posted by Scott Price

Try this:

Expand|Select|Wrap|Line Numbers

If Forms!frmFoodDonationbyDate.txtDonDate > Me.txtDistributionDate Then

Instead of this:

Expand|Select|Wrap|Line Numbers

If frmFoodDonationbyDate.txtDonDate > Me.txtDistributionDate Then

Regards,
Scott

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

Scott Price

Quote:

Originally Posted by Cintury

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

Quote:

Originally Posted by Killer42

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

Quote:

Originally Posted by Cintury

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

Cintury

Quote:

Originally Posted by Scott Price

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 frmFoodDonationbyDate referred to in your expression.

Do you think this is because it is hidden?

Expand|Select|Wrap|Line Numbers

 
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

Quote:

Originally Posted by Cintury

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 frmFoodDonationbyDate referred to in your expression.

Do you think this is because it is hidden?

Expand|Select|Wrap|Line Numbers

 
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:

Quote:

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("OrderForm")
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!NewData

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.Form!Controls.NewData

Forms!OrderForm.ctlSubForm!NewData

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 frmDonationByDate 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

Quote:

Originally Posted by 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?

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

Querying a recordset with VBA Variables

re: Querying a recordset with VBA Variables

re: Querying a recordset with VBA Variables

re: Querying a recordset with VBA Variables

re: Querying a recordset with VBA Variables

re: Querying a recordset with VBA Variables

re: Querying a recordset with VBA Variables

re: Querying a recordset with VBA Variables

re: Querying a recordset with VBA Variables

re: Querying a recordset with VBA Variables

re: Querying a recordset with VBA Variables

re: Querying a recordset with VBA Variables

re: Querying a recordset with VBA Variables

re: Querying a recordset with VBA Variables

re: Querying a recordset with VBA Variables

re: Querying a recordset with VBA Variables

re: Querying a recordset with VBA Variables

re: Querying a recordset with VBA Variables

re: Querying a recordset with VBA Variables

re: Querying a recordset with VBA Variables

re: Querying a recordset with VBA Variables

re: Querying a recordset with VBA Variables

re: Querying a recordset with VBA Variables

re: Querying a recordset with VBA Variables

re: Querying a recordset with VBA Variables

re: Querying a recordset with VBA Variables

re: Querying a recordset with VBA Variables

re: Querying a recordset with VBA Variables

re: Querying a recordset with VBA Variables

re: Querying a recordset with VBA Variables

re: Querying a recordset with VBA Variables

re: Querying a recordset with VBA Variables

re: Querying a recordset with VBA Variables

re: Querying a recordset with VBA Variables

Similar Microsoft Access / VBA bytes

/bytes/development

/bytes/it

/bytes/about