Mysterious - The Value you entered isn't valid for this field - Error

[TheSearcher]

I know. This should be an easy one - except for the fact that my combo box is unbound.
I have an option group that lets the user choose to search by name, date, therapy, payer etc.
If they choose Name (or anything else) then the rowsource for the combo box is dynamically populated accordingly.
When the form is first opened - if Date is chosen - everything works fine. However, if Name is chosen immediately after that then "The Value you entered isn't valid for this field" appears. As I mentioned, the combo box is unbound.
To make matters more mysterious, when the form is first opened - and something other than Date is selected first - then everything works properly even if Date is eventually chosen!
Has anyone ever experienced this? Very weird.

Thanks in advance,
TS

 

[gemma-the-husky]

What code do you have for the name and date selections? Note that name and date are both reserved words, and maybe that is the issue.

 

[TheSearcher]

Thanks for responding. The names of the fields are: Client_Name, Date_Of_Service.

 

[jdraw]

Have not experienced that.
If you post a copy of your database with enough data to show the issue, I'm sure someone will look and offer comments.

 

[bastanu]

Could it be that the form gets saved with some filter or order by properties, can you try to clear those in the open event of the form?

 

[The_Doc_Man]

If you do a search, do you clear out everything before you attempt the next search? Since the combo box is unbound, I don't believe it would be reset by a Form_Current event. Which means that it is possible for some residual value to be present. If the value is of the wrong data type AND is inconsistent with whatever was left over, that might explain it.

 

[MajP]

You have the Format property set to a date format.

 

[gemma-the-husky]

^^^
so the name isn't a legitimate date then.

 

[TheSearcher]

Thanks everyone for your input. I still can't determine what is wrong.
See attached sample. This is very weird.

Steps to reproduce error:
1.) Open frm_BillingReview
2.) Select Date of Service from the option group
3.) Select 1/3/2022 from the combo box
4.) Click the Get Data to Review button
5.) Click the Clear Data button
6.) Select Client Name from the option group
7.) Select Al Pacino from the combo box

ERROR OCCURRS

If you follow the same process and choose something other than Date of Service for Step 2 no errors will occur.

 

[jdraw]

I have not resolved anything, but I did add some code to print the query sql for each of the selections in the option group. It may offer some insight??

Debug.Print og_SearchBy & " - " & sql1  'jed

Results from some testing:

1 - SELECT [Client_FName] & ' ' & [Client_LName] & ' - ' & [Client_Id] AS Name FROM tbl_Billing GROUP BY [Client_FName] & ' ' & [Client_LName] & ' - ' & [Client_Id] ORDER BY [Client_FName] & ' ' & [Client_LName] & ' - ' & [Client_Id];
4 - SELECT tbl_Billing.Therapy FROM tbl_Billing GROUP BY tbl_Billing.Therapy ORDER BY tbl_Billing.Therapy;
5 - SELECT tbl_Billing.Payer FROM tbl_Billing GROUP BY tbl_Billing.Payer ORDER BY tbl_Billing.Payer;
1 - SELECT [Client_FName] & ' ' & [Client_LName] & ' - ' & [Client_Id] AS Name FROM tbl_Billing GROUP BY [Client_FName] & ' ' & [Client_LName] & ' - ' & [Client_Id] ORDER BY [Client_FName] & ' ' & [Client_LName] & ' - ' & [Client_Id];
2 - SELECT tbl_Billing.Date_Of_Service FROM tbl_Billing GROUP BY tbl_Billing.Date_Of_Service ORDER BY tbl_Billing.Date_Of_Service;
3 - SELECT tbl_Billing.Auth_No FROM tbl_Billing GROUP BY tbl_Billing.Auth_No ORDER BY tbl_Billing.Auth_No;
6 - SELECT tbl_Billing.Billed_By FROM tbl_Billing GROUP BY tbl_Billing.Billed_By ORDER BY tbl_Billing.Billed_By;
5 - SELECT tbl_Billing.Payer FROM tbl_Billing GROUP BY tbl_Billing.Payer ORDER BY tbl_Billing.Payer;
4 - SELECT tbl_Billing.Therapy FROM tbl_Billing GROUP BY tbl_Billing.Therapy ORDER BY tbl_Billing.Therapy;
3 - SELECT tbl_Billing.Auth_No FROM tbl_Billing GROUP BY tbl_Billing.Auth_No ORDER BY tbl_Billing.Auth_No;
2 - SELECT tbl_Billing.Date_Of_Service FROM tbl_Billing GROUP BY tbl_Billing.Date_Of_Service ORDER BY tbl_Billing.Date_Of_Service;
1 - SELECT [Client_FName] & ' ' & [Client_LName] & ' - ' & [Client_Id] AS Name FROM tbl_Billing GROUP BY [Client_FName] & ' ' & [Client_LName] & ' - ' & [Client_Id] ORDER BY [Client_FName] & ' ' & [Client_LName] & ' - ' & [Client_Id];

 

[jdraw]

Again, nothing resolved, but I added code in the cmd_GetData click in hopes of identifying "something"

 Debug.Print "chkSignoff is " & IIf(Me.chk_SignedOff = False, "False", "True") & " = " & sql1 'jed

This is the result for
a)DateofService 3 jan 22
b)Client Al Pacino

2 - SELECT tbl_Billing.Date_Of_Service FROM tbl_Billing GROUP BY tbl_Billing.Date_Of_Service ORDER BY tbl_Billing.Date_Of_Service;
chkSignoff is False = SELECT tbl_Billing.[Client_FName] & ' ' & [Client_LName] AS Name, tbl_Billing.Therapy, tbl_Billing.Payer, tbl_Billing.Date_Of_Service as DOS, tbl_Billing.Billed_By, FormatDateTime(tbl_Billing.Billing_Date,2) as Bill_Date, tbl_Billing.Number_UnitsBilled as Units_Billed, tbl_Billing.Client_Id, tbl_Billing.Code, tbl_Billing.ID FROM tbl_Billing WHERE tbl_Billing.Date_Of_Service = #03-Jan-22# AND tbl_Billing.Reviewed = False

1 - SELECT [Client_FName] & ' ' & [Client_LName] & ' - ' & [Client_Id] AS Name FROM tbl_Billing GROUP BY [Client_FName] & ' ' & [Client_LName] & ' - ' & [Client_Id] ORDER BY [Client_FName] & ' ' & [Client_LName] & ' - ' & [Client_Id];
chkSignoff is False = SELECT tbl_Billing.[Client_FName] & ' ' & [Client_LName] AS Name, tbl_Billing.Therapy, tbl_Billing.Payer, tbl_Billing.Date_Of_Service as DOS, tbl_Billing.Billed_By, FormatDateTime(tbl_Billing.Billing_Date,2) as Bill_Date, tbl_Billing.Number_UnitsBilled as Units_Billed, tbl_Billing.Client_Id, tbl_Billing.Code, tbl_Billing.ID FROM tbl_Billing WHERE tbl_Billing.Client_Id = '600002' AND tbl_Billing.Reviewed = False

 

[TheSearcher]

Thanks for your efforts jdraw. I really appreciate it. I used similiar debugging techniques but, so far, I still can't identify the issue.

 

[Minty]

It sounds as if Access is "caching" the data type for the combo.
Have you considered setting it to have the date field lookup as a default on form open, and then change/reset it after the option group is changed, and see if that fixes it?

That sort of forces it to the working settings from the word go?

 

[jdraw]

Thanks for your efforts jdraw. I really appreciate it. I used similiar debugging techniques but, so far, I still can't identify the issue.

I'm not getting data for Al Pacino. I get it for others, and I can get/see Al Pacino records when searching by other options.

I think Minty may have identified a working arrangement.

 

[TheSearcher]

jdraw - Al pacino doesn't have any records that fit the criteria.

 

[TheSearcher]

It sounds as if Access is "caching" the data type for the combo.
Have you considered setting it to have the date field lookup as a default on form open, and then change/reset it after the option group is changed, and see if that fixes it?

That sort of forces it to the working settings from the word go?

Since the error occurs when the "Date" option is chosen first it wouldn't make sense to have that as the default. But - I made the "Name" option the default and changed the rowsource of the combo box accordingly on the form's On Open event and this seems to have worked. I would prefer to have no default - but, at this point, I'm happy with any victory I can get!
Thank you Minty! And thanks to everyone else too!

 

[jdraw]

Fair enough, but his records show up with other criteria.

 

[Minty]

Since the error occurs when the "Date" option is chosen first it wouldn't make sense to have that as the default. But - I made the "Name" option the default and changed the rowsource of the combo box accordingly on the form's On Open event and this seems to have worked. I would prefer to have no default - but, at this point, I'm happy with any victory I can get!
Thank you Minty! And thanks to everyone else too!

My bad I mis-read your OP, but you got the jist of what I meant - glad it's sorted out even if bit of a kludge.

 

[The_Doc_Man]

A sort of a light-bulb just flashed on. It sounds like the presence of a default is the key, and that the Date field was initially your default. Thus it would have been possible to somehow get through without reconditioning some control. Choosing a different default is less important than the fact that the chosen default WASN'T a date. Any other non-date default might have worked as well.

A different approach would be to have NO default and to instead take extreme pains to assure that someone selects something. For combo boxes, that test means "Do not allow past the BeforeUpdate event if the combo's .ListIndex is still -1." It ALSO means that when resetting for the next selection, you should perform a combo.Undo, which will reset everything internally for that combo.

 

[MajP]

Did you check the format property as previously stated?