Need Fancy Prairie Fancy Access Footwork

[AustinMan]

I have been talking to blanch about my problem.

I can not seem to figure this glich.

If you have additional questions, let me know.

Refering to this thread: thread702-491293

Can you shed some light. I may not been giving blanch enough specific structure information about the Form, queries, recordsets, etc... that might be causing this problem.

Please help.

Thanks for your time.

 

[FancyPrairie]

I'm having trouble understanding what you're doing. But this is what I think. You have 2 comboboxes. The user enters (selects) a value from each of the comboboxes. At some point (i.e. Form_AfterUpdate event), you set the RecordSource of the form to a SQL statement where the Where clause is dependent upon the values of the 2 comboboxes. If the SQL statement returns records, everything is OK. If not, you have problems using the comboboxes again.

I created a form (based on a query) and put 2 comboboxes in the Form header. I also added a command button in the Form header. I selected a value from each combobox and clicked on the command button. The OnClick event of the command button rebuilt my Recordsource for the form so that it limited the records to between the value of combobox 1 and combobox2. For example,

Me.Recordsource = &quot;Select * from MyTable where ID >= &quot; & combobox1.value & &quot; and ID <= &quot; & combobox2.value

Everything works fine regardless of whether I put valid values in or invalid values.

Therefore, I suggest, you start by simplifying what you're doing. Instead of several if..then...else statments, just set you RecordSource property to something simple (like what I did). If that works, add more complexity to it until you find out what causing the problem. I don't see how, what you're doing, can cause a problem with your comboboxes. (Unless you're messing with subforms)

 

[MichaelRed]

I certainly do not want to leap into the deep (or shallow) end of the pool. But ... one problem can result of the technique can occur for bound forms if the recordsource has no records. The &quot;form&quot; may shgow NO controls at all. This depends somewhat on the FORM properties Allow* [Edit | Addition | ... ]. Overall, a somewhat less troubling approach would be to use the entered values to navigate to to the desired / selected record in the RECORDSETCLONE, and set the form's (recordset) bookmark to the found record, after checking that a record WAS found from the entered criteria (how to check depends on the data access method chosen). Of course, you still need to take care in the form's property settings, as having an inappropiate combination of settings and recordsources may be problematic.

MichaelRed
m.red@att.net

Searching for employment in all the wrong places

 

[AustinMan]

The SQL statements work fine for me.

I am trying to provide summary totals based on the selection criteria.

1. Summary Totals by Professional Type for all Regions and ALL Agency Types (Grand Total)
Both Combo1.Value & Combo2 Value is selected to &quot;ALL&quot;

Entered SQL Statement not using variables Agency Type nor Region fields in the statement for summary.

2. Summary Totals by Professional Type for a Specific Region for ALL Agency Types
Combo1.Value = specific Region & Combo2.Value = &quot;ALL&quot;

Entered SQL Statement including Specific Region field in SQL Statement and not including Combo2.Value or Agency field in SQL Statement for summary.

3. Summary Totals by Professional Type for a ALL Regions by a specific Agency Type.
Combo1.Value = &quot;ALL&quot; & Combo2.Value = specific Agency Type

Entered SQL Statement including Specific Agency Type field in SQL statement and not including Combo1.value or Region field in SQL Statement.

4. Summary Totals by Professional Type for a specific region & by a specific Agency Type
Combo1.Value = specific Region field & Combo2.Value=specific Agency Type

Entered SQL Statement including Region Field and Agency Type fields in SQL Statement.

I am using &quot;ALL&quot;, which is not found in the underlying table; adding by entering a union query on top of actual combobox values for agency type and Region respectively.

The purpose is for the user to select either one or both and query. I tried to cover the four options the user can take.

I believe the first 3 scenarios worked without a hitch.

The difficulty I am having is when a specific Region and a specific Agency Type to not have records based on the selected criteria. The SQL statements results into no records fiting the criteria selected.

These comboboxes (both) are unbounded, based on union queries as rowsources. (needed to include &quot;ALL&quot; as Selection choice)

In my form code, based on the Statements, do I need to reset the form recordset, or How do I reset the comboboxes again. Each combobox brings in the accepted values for each respective combobox. Now, the form did not bring any results, blank. Once I try to make a selection again on either combobox, the value flashes but the selected value is not visible.

If I re-query again, any the SQL statements results with records that match that criteria, It brings up those records but the combobox still do not show visually the selection criteria.

However, if I go back into the combobox, it somehow knows the value for its respective combobox and has the selection on that value.

I hope I am not bothering you. I just do not know if that process I am using to re-query is appropriate or cause for this problem (I do not think so).

I just think some values of the Combo1.Value & Combo2.Value are not sticking or being orphaned.

Form has no recordsource, only when the Requery is entered.

The Form is only allowed edits, no additions, no deletions.

Any additional Suggestions.

Thanks in advance.

 

[MichaelRed]

per my earlier post. you are setting form properties (allow ?) and setting recordsource. no resulte in recordsource ==> no display (and other resulting issues). see the earlier post. position forms recordset to the found entry (when it is found) via bookmark manipulation when record is not found, provide user feedback via msg box and position form recordsource to arbitrary (1st?) record.


MichaelRed
m.red@att.net

Searching for employment in all the wrong places

 

[AustinMan]

How does one use the bookmark property.

I am not sure this can be accomplished.


Thanks again. %-)

 

[AustinMan]

Can someone look at my form in my database?