Some combo boxes work, some don't

[AndyBr]

This has been most obnoxious for me over the last day or so, and I'd appreciate any help people can give me.

I have a database used to record the results of monitoring the Department of Health does of providers. Providers are monitored annually, so every record is identified by a provider name and monitoring date (and item ID but that's beyond the scope here).

The users are data entering in a form, and the first thing they do is enter the provider name and monitoring date into combo boxes on the form, then click a command button that runs a query to put the provider name and monitoring date together with the items and insert them into the working table.

This has worked for a year or so in a system where I programmed on Access97 and the users were in 2002 (it's the government).

This year, updated everyone to 2002. Now the Provider Name works, but the Monitoring date returns a blank. So I made a dummy query with just those items, and the monitoring date still returns as blank. From that, I'm concluding that the issue is with the form.

I can't see any difference in the combo box properties.

I've over-simplified this a little. I ran my text query straight from the data entry, without any additional form code or functionality (the only thing it does it that on form open it sets some things visible and others not visible) so I don't think any of the other parts of the database are having any effect (plus they worked last year), but the whole thing is a bit much to paste the code.

Pulling my hair out on this one. Can anyone shed any light on it?

Andy

EDIT: Searching through the forums and didn't find anything close to an answer. I see a common issue is filtering a query or table using a combo box. What's I'm doing is different: I have an append query, and I want to take the combo box value and use it as a field in the append query. How to do that is not the issue - it works for other combo boxes in the same form and it worked all of last year. I'm thinking it might be something in 2002 that I'm not familiar with.

 

[wiklendt]

more relevant info please-

what you have NOT given, despite the length of your descriptions, is ANY code/detail at all - what are your sources for your combo boxes? (i.e., SELECT... FROM... ORDER BY...)?

what is the code behind your button?

if your (i assume, because you have not REALLY said what you've done)plain query to filter Monitoring date is faulty, i would suggest it's an SQL issue, NOT a form issue.

btw, if your Monitoring date field is called "Date", this could cause issues.

 

[AndyBr]

Thanks. I realize how hard it is to comment without code.

Here's what I have:

In form's field is named "Add_Monitoring_Date". It is sourced by a query, but since people will be entering new data, it's not limited to the query data. (The query queries the past entries for that provider, so if someone entered 1/21/2010 yesterday, it is less likely they will enter 1/22/2010 today; code is below).


What I'm doing now to isolate problem is going in, adding a provider name and monitoring date, then minimizing the form. The only things that have happened in the form are:

1. On form open, some boring appearance stuff

    DoCmd.Maximize
 
        'Visible, but set it to a starting value
        Me![Action] = ""
        'Shows the items based on tools or source
        Me![Show].Visible = False
        Me![Show_Tools].Visible = False
        Me![Show_Nmbr].Visible = False
        Me![Show_Source1].Visible = False
        Me![Show_Source2].Visible = False
        Me![cmd_Open_Items_Form].Visible = False
        
        
        'Adds the tools to the various tables
        Me![Add_Tool].Visible = False
        Me![Add_Year].Visible = False
        Me![Add_Number].Visible = False
        Me![Add_Service_wTool].Visible = False
        Me![Label_Add_Service_wTool].Visible = False
        Me![Cmd_Add_Queries].Visible = False
        Me![qry_Support_Tool_Add_List1 subform Label].Visible = True
        Me![qry_Support_Tool_Add_List1 subform].Visible = True
                
        'Administrative functions
        Me![Select_Admin].Visible = False
        Me![Tool_List_For_Qry].Visible = False
        Me![cmd_Admin_Button].Visible = False
        
        'Pr Reports
        Me![Select_Pr_Reports].Visible = False
        Me![cmd_Report].Visible = False

2. The query which is the source of the monitoring date:

SELECT 
tbl_Main_Prov.Monitoring_Date, 
tbl_Main_Prov.Provider
FROM tbl_Main_Prov
WHERE (((tbl_Main_Prov.Provider)=[Forms]![frm_Main_Start]![Add_Provider]));

3. Going in and out of that combo box:

Private Sub Add_Monitoring_Date_GotFocus()
    Me![Add_Monitoring_Date].Requery
End Sub
 
Private Sub Add_Monitoring_Date_LostFocus()
    DoCmd.ShowAllRecords
End Sub

Again, I'm going in, adding a provider name and monitoring date, then minimizing. So, having only done that on the form, I try to run a simple query from that. Something like:

SELECT 
[Forms]![frm_Main_Start]![Add_Provider] AS Expr1, 
[Forms]![frm_Main_Start]![Add_Monitoring_Date] AS Expr2
FROM tbl_Main_Item_Detail;

Which returns the provider (Add_Provider) but not the date (Add_Monitoring_Date).

They are set up the exact same way, and this worked last year, so I'm scratching my head.

I haven't touched on the code button issue. That's because I've isolated the problem to it's simplest components by mkaing the dummy query above. If you are interested, it is:

Private Sub Cmd_Add_Monitoring_DblClick(Cancel As Integer)
 
Dim stDocName As String

If IsNull(Me![Add_Provider]) = True Then
        MsgBox ("You must indicate a provider")
ElseIf IsNull(Me![Add_Monitoring_Date]) = True Then
        MsgBox ("You must indicate a monitoring date")
Else
        'Remove Access warnings
            DoCmd.SetWarnings False
        'Add monitoring data
            stDocName = "qry_Add_Prov"
            DoCmd.OpenQuery stDocName, acNormal, acEdit
        'Restore Access warnings
            DoCmd.SetWarnings True
End If

I didn't mean to keep anything from anyone, it's just that I don't see a lot in that basic code that could be awry. And, as I mentioned, it worked all of last year with the only change being the version of Access.

If you or anyone has any ideas, they would be greatly appreciated. It looks like I'm taking this one home this weekend.


Andy

 

[wiklendt]

it worked all of last year with the only change being the version of Access.

standard code does change over time, as does the way different versions of access handles the code. sometimes newer access versions can be more stringent, sometimes it's more relaxed.

i've seen various bits of code have stopped working all together with an upgrade throughout these fora. the code vbaInet picked you up on could just be one of those...

it sucks, i know - keeps us developers on our toes.

let us know if vbaInet's solution works for you.

 

[vbaInet]

standard code does change over time, as does the way different versions of access handles the code. sometimes newer access versions can be more stringent, sometimes it's more relaxed.

i've seen various bits of code have stopped working all together with an upgrade throughout these fora. the code vbaInet picked you up on could just be one of those...

it sucks, i know - keeps us developers on our toes.

let us know if vbaInet's solution works for you.

i had to withdraw my statement on that sql string. i had a second thought and was only 85% sure that that was the correct syntax.

however if you still need it, here's the last line i initially gave

WHERE (((tbl_Main_Prov.Provider)='" & [Forms]![frm_Main_Start]![Add_Provider])) & "';

WHERE (((tbl_Main_Prov.Provider)=" & [Forms]![frm_Main_Start]![Add_Provider] & "));

first line for string data type and the second for integer.

 

[wiklendt]

i had to withdraw my statement on that sql string. i had a second thought and was only 85% sure that that was the correct syntax.

however if you still need it, here's the last line i initially gave

WHERE (((tbl_Main_Prov.Provider)='" & [Forms]![frm_Main_Start]![Add_Provider])) & "';

WHERE (((tbl_Main_Prov.Provider)=" & [Forms]![frm_Main_Start]![Add_Provider] & "));

first line for string data type and the second for integer.

i've always had trouble with quotation marks in my SQLs. i normally just play around with it until i get it right.

as an additional note, if the setup is correct, the provider ought to be filtered by ID, so technically would be an integer. this would be true even for comboboxes, because the bound column of that combobox ought to be, in this example, the ProviderID. I'm worried the OPs "Provider" might not actually be the primary key...

 

[vbaInet]

i've always had trouble with quotation marks in my SQLs. i normally just play around with it until i get it right.

as an additional note, if the setup is correct, the provider ought to be filtered by ID, so technically would be an integer. this would be true even for comboboxes, because the bound column of that combobox ought to be, in this example, the ProviderID. I'm worried the OPs "Provider" might not actually be the primary key...

you're absolutely right there. i think it's a joint primary key. maybe the poster could clarify?

by the way andybr, post your "qry_Add_Prov" query.

 

[AndyBr]

Thanks Wiklendt and vbaInet for the thoughts and answers. You guys were right, the issue wasn't in the forms - or, better, the solution was in the query.

What you wrote was accurate, but didn't exactly fit. But it prompted something.

The solution was to use the format function to re-format it as a date.

The form combo box is formatted as a date, and in the table it draws from (through the query I wrote as point #2 below) the field is formatted as a date, so I don't know at what point it lost that. I chalk that up to the change in versions. Kind of frustrating not to know the 'why' but that's life.



BTW, I don't have the query handy - it's the weekend and I have my work laptop at home non-networked (I'm writing on my own machine). The primary key, though, is another field, which itself is a concatenation of the ProviderID and Monitoring date. Provider ID by itself wouldn't suffice, as providers are monitored every year, so there would be duplicates. It builds up: there's one provider table with a primary key for provider code, one provider-date table with a primary key the concate field I mentioned earlier, and one provider-date-item table with all the questions/items. (It gets a bit long to post.)



For those who might benefit from reading this topic, the issue with 'some work, some don't' should have prompted something about the difference between the ones that worked and the one that didn't: the formatting.

The more specific solution was, in the query:

Format([Add_Monitoring_Date],'mm/dd/yyyy') AS Monitoring_Date

I added it in SQL, but one could as easily do it directly in the Access query GUI.


Thanks again, Wiklendt and vbaInet

Andy

 

[vbaInet]

Thanks Wiklendt and vbaInet for the thoughts and answers. You guys were right, the issue wasn't in the forms - or, better, the solution was in the query.

What you wrote was accurate, but didn't exactly fit. But it prompted something.

The solution was to use the format function to re-format it as a date.

The form combo box is formatted as a date, and in the table it draws from (through the query I wrote as point #2 below) the field is formatted as a date, so I don't know at what point it lost that. I chalk that up to the change in versions. Kind of frustrating not to know the 'why' but that's life.



BTW, I don't have the query handy - it's the weekend and I have my work laptop at home non-networked (I'm writing on my own machine). The primary key, though, is another field, which itself is a concatenation of the ProviderID and Monitoring date. Provider ID by itself wouldn't suffice, as providers are monitored every year, so there would be duplicates. It builds up: there's one provider table with a primary key for provider code, one provider-date table with a primary key the concate field I mentioned earlier, and one provider-date-item table with all the questions/items. (It gets a bit long to post.)



For those who might benefit from reading this topic, the issue with 'some work, some don't' should have prompted something about the difference between the ones that worked and the one that didn't: the formatting.

The more specific solution was, in the query:

Format([Add_Monitoring_Date],'mm/dd/yyyy') AS Monitoring_Date

I added it in SQL, but one could as easily do it directly in the Access query GUI.


Thanks again, Wiklendt and vbaInet

Andy

You're welcome Andy. You don't necessary need to call the Format function, just wrap your date in hash characters. That tells the jet engine that the value returned is a date. That is #[Add_Monitoring_Date]#

let me know if that works.

 

[AndyBr]

You're welcome Andy. You don't necessary need to call the Format function, just wrap your date in hash characters. That tells the jet engine that the value returned is a date. That is #[Add_Monitoring_Date]#

let me know if that works.

That's originally what I tried. For some reason, it wanted to read the whole thing as text.

That is, as:

"#[Add_Monitoring_Date]#"

Rather than:

1/25/2010

I fiddled a little with single and double quotes, but quickly just went with the format function. I just didn't spend enough time fiddling to make it work that way.

Thanks again,

Andy

 

[vbaInet]

That's originally what I tried. For some reason, it wanted to read the whole thing as text.

That is, as:

"#[Add_Monitoring_Date]#"

Rather than:

1/25/2010

I fiddled a little with single and double quotes, but quickly just went with the format function. I just didn't spend enough time fiddling to make it work that way.

Thanks again,

Andy

Excellent. Glad to know it's all working fine.