By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
444,124 Members | 2,208 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 444,124 IT Pros & Developers. It's quick & easy.

Querying a recordset with VBA Variables

Cintury
P: 81
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
  1. Private Sub txtDistributionDate_LostFocus()
  2. Dim rs As DAO.Recordset
  3. Dim DB As DAO.Database
  4. Dim tempDate As Date
  5. Dim tempFieldID, strSQL As String
  6.  
  7.  Set DB = CurrentDb()
  8.  
  9.   Me.cboFoodDonationsID.SetFocus
  10.   tempFieldID = Me.cboFoodDonationsID.Text
  11.   MsgBox tempFieldID, vbOKOnly
  12.  
  13.  
  14.   Set rs = DB.OpenRecordset("SELECT Date FROM tblFoodDonations WHERE [tblFoodDonations]![FoodDonationsID] ='" & tempFieldID & " ' ")
  15.     rs.MoveFirst
  16.  
  17.     Do While Not rs.EOF
  18.     rs.MoveNext
  19. 'TEST to see the current seached record
  20.     MsgBox rs("FoodDonationsID").Value + rs("Date").Value, vbOKOnly
  21.  
  22.     Loop
  23.     tempDate = rs("Date").Value
  24.  
  25.  
  26.  
  27.   If Me.txtDistributionDate > tempDate Then
  28.   MsgBox "Please enter a date that is after the product was Donated!", vbExclamation, "Date Check"
  29.  
  30.  
  31.  
  32.   Me.txtDistributionDate.SetFocus
  33.  
  34.   End If
  35.  
  36.   rs.Close
  37.  
  38. End Sub
  39.  
Thank you in advanced for your help!
Aug 10 '07 #1
Share this Question
Share on Google+
33 Replies


Scott Price
Expert 100+
P: 1,384
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
  1. 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
Aug 11 '07 #2

Scott Price
Expert 100+
P: 1,384
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
Aug 11 '07 #3

Expert 5K+
P: 8,434
Ignore me, I'm just registering an interest in this thread so I'll be notified of updates.
Aug 12 '07 #4

Cintury
P: 81
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
  1. 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!
Aug 13 '07 #5

Scott Price
Expert 100+
P: 1,384
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?
Aug 13 '07 #6

Cintury
P: 81
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
  1. Private Sub txtDistributionDate_AfterUpdate()
  2. Dim tempFieldID As String
  3.  
  4. Me.cboFoodDonationsID.SetFocus
  5. tempFieldID = Me.cboFoodDonationsID.Value
  6.  
  7. DoCmd.OpenForm "frmFoodDonationbyDate", , , , , acHidden
  8. -> frmFoodDonationbyDate.Filter = "[FoodDonationsID]" & tempFieldID
  9. frmFoodDonationbyDate.FilterOn = True
  10.  
  11. If frmFoodDonationbyDate.txtDonDate.Value > txtDistributionDate.Value Then
  12.  MsgBox "Please enter a Delivery date that is after that of the date of Donation!", vbOKOnly, "Out of Range Date Exception"
  13.  txtDistributionDate.SetFocus
  14.  End If
  15.  
  16. DoCmd.Close acForm, "frmFoodDonationbyDate"
  17. End Sub
  18.  
Thank you in advance for the advice
Aug 14 '07 #7

Scott Price
Expert 100+
P: 1,384
Instead of this:
DoCmd.OpenForm "frmFoodDonationbyDate", , , , , acHidden
-> frmFoodDonationbyDate.Filter = "[FoodDonationsID]" & tempFieldID
frmFoodDonationbyDate.FilterOn = True
Try this:

Expand|Select|Wrap|Line Numbers
  1. 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
Aug 14 '07 #8

Cintury
P: 81
Instead of this:


Try this:

Expand|Select|Wrap|Line Numbers
  1. 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
  1. Private Sub txtDistributionDate_AfterUpdate()
  2. Dim tempFieldID As String
  3. Dim frmFoodDonationbyDate As Form
  4.  
  5. Me.cboFoodDonationsID.SetFocus
  6. tempFieldID = Me.cboFoodDonationsID.Value
  7.  
  8. -> DoCmd.OpenForm "frmFoodDonationbyDate", , , , "[FoodDonationsID]=" & tempFieldID, acHidden
  9.  
  10.  
  11. If frmFoodDonationbyDate.txtDonDate > txtDistributionDate Then
  12.  MsgBox "Please enter a Delivery date that is after that of the date of Donation!", vbOKOnly, "Out of Range Date Exception"
  13.  End If
  14.  
  15. DoCmd.Close acForm, "frmFoodDonationbyDate"
  16. End Sub
  17.  
Aug 14 '07 #9

Scott Price
Expert 100+
P: 1,384
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
  1. Private Sub txtDistributionDate_AfterUpdate()
  2. Dim tempFieldID As String
  3. Dim frmFoodDonationbyDate As Form
  4.  
  5. Me.cboFoodDonationsID.SetFocus
  6. tempFieldID = Me.cboFoodDonationsID.Value
  7.  
  8. -> DoCmd.OpenForm "frmFoodDonationbyDate", , , , "[FoodDonationsID]=" & tempFieldID, acHidden
  9.  
  10.  
  11. If frmFoodDonationbyDate.txtDonDate > txtDistributionDate Then
  12.  MsgBox "Please enter a Delivery date that is after that of the date of Donation!", vbOKOnly, "Out of Range Date Exception"
  13.  End If
  14.  
  15. DoCmd.Close acForm, "frmFoodDonationbyDate"
  16. End Sub
  17.  
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
Aug 14 '07 #10

Cintury
P: 81
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
  1. Private Sub txtDistributionDate_AfterUpdate()
  2. Dim tempFieldID As String
  3. Dim frmFoodDonationbyDate As Form
  4.  
  5. Me.cboFoodDonationsID.SetFocus
  6. tempFieldID = Me.cboFoodDonationsID.Value
  7.  
  8. (A)-> DoCmd.OpenForm "frmFoodDonationbyDate", , , "[frmFoodDonationbyDate]![FoodDonationsID] =" & tempFieldID, , acHidden
  9. DoCmd.Requery
  10.  
  11. (B)->If [frmFoodDonationbyDate]![txtDonDate] > Me.txtDistributionDate Then
  12.  MsgBox "Please enter a Delivery date that is after that of the date of Donation!", vbOKOnly, "Out of Range Date Exception"
  13.  Me.txtDistributionDate.SetFocus
  14.  End If
  15.  
  16. DoCmd.Close acForm, "frmFoodDonationbyDate"
  17. End Sub
  18.  
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.
Aug 14 '07 #11

Scott Price
Expert 100+
P: 1,384
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
Aug 14 '07 #12

Cintury
P: 81
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.
Aug 14 '07 #13

Scott Price
Expert 100+
P: 1,384
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
Aug 14 '07 #14

Scott Price
Expert 100+
P: 1,384
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
  1.       Private Sub txtDistributionDate_AfterUpdate()
  2.       Dim tempFieldID As String
  3.       Dim frmFoodDonationbyDate As Form
  4.  
  5.       Me.cboFoodDonationsID.SetFocus
  6.       tempFieldID = Me.cboFoodDonationsID.Value
  7.  
  8.       (A)-> DoCmd.OpenForm "frmFoodDonationbyDate", , , "[FoodDonationsID] =" & tempFieldID, , acHidden
  9.       DoCmd.Requery
  10.  
  11.       (B)->If [frmFoodDonationbyDate]![txtDonDate] > Forms![YourMainFormName]![YourSubformName].txtDistributionDate Then
  12.        MsgBox "Please enter a Delivery date that is after that of the date of Donation!", vbOKOnly, "Out of Range Date Exception"
  13.        Me.txtDistributionDate.SetFocus
  14.        End If
  15.  
  16.       DoCmd.Close acForm, "frmFoodDonationbyDate"
  17.       End Sub
Regards,
Scott
Aug 14 '07 #15

Cintury
P: 81
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.
Aug 15 '07 #16

Scott Price
Expert 100+
P: 1,384
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
Aug 15 '07 #17

Cintury
P: 81
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
  1. Private Sub txtDistributionDate_AfterUpdate()
  2. Dim tempFieldID As String
  3. Dim frmFoodDonationbyDate As New Form
  4.  
  5. Me.cboFoodDonationsID.SetFocus
  6. tempFieldID = Me.cboFoodDonationsID.Value
  7.  
  8. -> DoCmd.OpenForm "frmFoodDonationbyDate", , , [frmFoodDonationbyDate]![FoodDonationsID] & "=" & tempFieldID, , acHidden
  9. DoCmd.Requery
  10.  
  11. If [frmFoodDonationbyDate]![txtDonDate] > Me.txtDistributionDate Then
  12.  MsgBox "Please enter a Delivery date that is after that of the date of Donation!", vbOKOnly, "Out of Range Date Exception"
  13.  Me.txtDistributionDate.SetFocus
  14.  End If
  15.  
  16. DoCmd.Close acForm, "frmFoodDonationbyDate"
  17. End Sub
  18.  
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
Aug 15 '07 #18

Scott Price
Expert 100+
P: 1,384
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
  1. 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
  1. 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
Aug 15 '07 #19

Cintury
P: 81
...
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
  1. SELECT tblFoodDonations.FoodDonationsID, tblFoodDonations.Date
  2. FROM tblFoodDonations;
  3.  
Aug 15 '07 #20

Scott Price
Expert 100+
P: 1,384
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
  1. SELECT tblFoodDonations.FoodDonationsID, tblFoodDonations.Date
  2. FROM tblFoodDonations;
  3.  
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
Aug 15 '07 #21

Cintury
P: 81
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
  1. SELECT tblFoodDonations.FoodDonationsID, tblFoodDonations.Date
  2. FROM tblFoodDonations
  3. WHERE (((tblFoodDonations.FoodDonationsID)=[Forms]![frmAgencyDonation]![cboFoodDonationsID]));
  4.  
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
  1. DoCmd.OpenForm "frmFoodDonationbyDate", , , , , acHidden
  2.  
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!
Aug 15 '07 #22

Scott Price
Expert 100+
P: 1,384
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
Aug 15 '07 #23

Cintury
P: 81
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
  1. If frmFoodDonationbyDate.txtDonDate > Me.txtDistributionDate Then
  2.  
I've verified that the control name is correct so right now I'm at a lost as to what is causing it.
Aug 15 '07 #24

Scott Price
Expert 100+
P: 1,384
Try this:

Expand|Select|Wrap|Line Numbers
  1. If Forms!frmFoodDonationbyDate.txtDonDate > Me.txtDistributionDate Then
Instead of this:

Expand|Select|Wrap|Line Numbers
  1. If frmFoodDonationbyDate.txtDonDate > Me.txtDistributionDate Then

Regards,
Scott
Aug 15 '07 #25

Cintury
P: 81
Try this:

Expand|Select|Wrap|Line Numbers
  1. If Forms!frmFoodDonationbyDate.txtDonDate > Me.txtDistributionDate Then
Instead of this:

Expand|Select|Wrap|Line Numbers
  1. If frmFoodDonationbyDate.txtDonDate > Me.txtDistributionDate Then

Regards,
Scott
Arrrg... Runtime error 2447 invalid use of the .(dot) or ! operator or invalid parenthesis.
Aug 15 '07 #26

Scott Price
Expert 100+
P: 1,384
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
Aug 16 '07 #27

Expert 5K+
P: 8,434
Also, it might be helpful to know exactly what VBA pointed at in the code, when it reported the error.
Aug 16 '07 #28

Cintury
P: 81
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.
Aug 16 '07 #29

Scott Price
Expert 100+
P: 1,384
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
Aug 16 '07 #30

Cintury
P: 81
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
  1. DoCmd.OpenForm "frmFoodDonationbyDate", , , , , acHidden
  2. DoCmd.Requery
  3.  
  4. -> If Form!frmFoodDonationbyDate.txtDonDate > Me.txtDistributionDate Then
  5.  
BTW the DAO and ActiveX are up to date, the only update not installed was VS 2005 sp1 and I just installed and rebooted.
Aug 16 '07 #31

Cintury
P: 81
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?
Aug 16 '07 #32

Scott Price
Expert 100+
P: 1,384
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
  1. DoCmd.OpenForm "frmFoodDonationbyDate", , , , , acHidden
  2. DoCmd.Requery
  3.  
  4. -> If Form!frmFoodDonationbyDate.txtDonDate > Me.txtDistributionDate Then
  5.  
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("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
Aug 16 '07 #33

Scott Price
Expert 100+
P: 1,384
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
Aug 16 '07 #34

Post your reply

Sign in to post your reply or Sign up for a free account.