Populating data from a table with multiple fields

[Asoul]

So many questions relating to this here and elsewhere, but I just can't equate any of the solutions to my challenge.

I am trying to create the form so when the first field (Start Date) has a date selected, it limits the following field (Report Date) to the matching available data, and same with the third field (Production Division) on the previous two.

I also need the form to allow new dates / production division combinations, but not duplicate.

I starting working with a cascade function but got confused and not sure if that is where I should be headed. Assistance is greatly appreciated; I have attached 2 different formats of the same sample data.

Thanks

 

[RainLover]

All you need is a search Combo.

You can't select the same data and add that to the table so you need to find the record you already have. If that does not exist then create a new one.

Does this make sense????

 

[Asoul]

Rain,

I guess I was hoping to accomplish this with list boxes instead of a combo box.
I used this post from John Booty http://www.access-programmers.co.uk/forums/showthread.php?t=188663 to create a search combo, and realized his (or someone's point I read) point regarding this not allowing data duplication, but guess I don't see how to fully implement this solution.

I was hoping to just keep the existing form in the previously attached DB and set the Report Date drop list to key off of selected value of Start Date and provide only matching alternatives in the drop list. The Production Division drop list would do the same off selected values of Start Date and Report Date.

Does that make sense?

I saw this example that was close in a post regarding cascading combo boxes, working to find it again.

 

[RainLover]

The Table that you have. Is this simply a lookup table so that you can store the information in a different table.

The way you have presented it so far is that you can only use the existing table as is.

 

[Asoul]

Rain, I removed the other fields/columns which relate to inventory values of the date ranges and associated Production Divisions of the table. The form is intended to allow the user to report off an existing date, evaluate an existing date, or enter a new date range-Production Division combination and evaluate and report. All queries/reports use the fields on this form of Start Date, Report Date, and Production Division in order to produce results.

 

[RainLover]

The form is intended to allow the user to report off an existing date, evaluate an existing date, or enter a new date range-Production Division combination and evaluate and report.

I am sorry but this sentence does not help at all.

Rain, I removed the other fields/columns which relate to inventory values of the date ranges and associated Production Divisions of the table.

You only have three Fields, StartDate, ReportDate and ProductDivision. You need to be more specific in your explanations. Which Fields did you delete.

All queries/reports use the fields on this form of Start Date, Report Date, and Production Division in order to produce results.

These are the original fields that were in your Sample Data base.

Is your situation that you want to use the Fields in the Sample Once and then have attached to them many other records.

Try and keep your question simple and only talk about things that I know about. If I don't know about something then you need to explain.

The word evaluate can mean different things. It could mean something to do with numbers I don't know.

 

[Asoul]

Rain,
My apologies for the delay; let me see if I can better explain:

Other fields included in the original table are all related to Inventory quantity and value totals i.e. Beginning Qty, Beginning Value, Planted Qty, Planted Value, etc. (I have re-attached an updated .mdb with those fields if that helps).

What this table does is hold the Inventory Totals of each period (Start Date to Report Date) for each Production Division.

When I say "evaluate", I mean run the process (set of queries) that calculates the inventory values for each date range (Period) and Production Division. We may want "evaluate" a Period from the first of the year, or for a specific single month, or an interim number of months within or crossing over years. Basically, there is no standard for the period that might be evaluated; hence any date range is possible.

Once a date range is added to the table though, I store the resulting evaluation Period and its associated inventory values in this table.

My goal is to keep the Start Date, Report Date, Production Division fields on the form set up as on Frm_InventorySummary but force the combo box to only show a Report Date that matches an existing Start Date, and also with Production Division.
You will also see I added another form "Frm_SearchMulti" which was a recommendation I found else where on the site that combines the three fields as one search parameter, but this is not really what I want to accomplish.

I am trying to be as clear as I can, but please let me know if I missed anything. Thanks again for your time and patience!

 

[RainLover]

Asoul

Could you please attach a file in A 2003 like you did the first time.

 

[Asoul]

My bad - accidentally reconverted to 2010. Here it is...

 

[RainLover]

I think I understand what you want.

If you select "1/01/2012" then you want to restrict "ReportDate" to those matching the Start Date and then restrict to "ProductDivision."

If this is what you want then you need to use a Cascading Combo Box. If you search this site you will find some examples.

May I also suggest that you read up on Naming Conventions. You should not have spaces in any of the names.

Does this help at all.

 

[Asoul]

Rain,

Unfortunately this doesn't help.
I have found plenty of examples of Cascading Combo Box, but can't get any of them to work for this application. (I even mentioned that I was working with them in my initial post).
I am really weak on the Form side of things and just not managing to convert the code I find and get anything to work correctly. Guess I'll just keep hacking...

Thanks for your time -

A

 

[RainLover]

Your problem can be solved.

I guess I am just not understanding.

Explain what the first combo should do and I will fix that and repost.

Then we can work on combo 2.

Please use simple English rather than technical talk. Sometimes tech talk loses its correct meaning.

 

[Asoul]

I resolved many of the issues I had been attempting to resolve with the attached version. Now I am having trouble with the code to get the form to go to the record selected by the Combo boxes. If you use the form you will see all fields start blank, the combo's display proper applicable values, but on update of the 3rd combo I can't get the code right to go to that record. If you follow the debug you will see the line I am referring to:

Private Sub ProductDivision_AfterUpdate()
DoCmd.GoToRecord , , "[Forms!Frm_InventorySummary.[Start Date]] = tbl_InventorySummary.[StartDate]" & "[Forms!Frm_InventorySummary.ReportDate] = tbl_InventorySummary.ReportDate" & "[Forms!Frm_InventorySummary.ProductDivsion] = tbl_InventorySummary.ProductDivision"
End Sub

If this code did work I believe I would be running into the issue of making the values of the combo boxes visible again, but at least this progress may give you a better feel for what I am trying to accomplish.

Thanks again for sticking with me - Peace

A

 

[RainLover]

Asoul

I looked at your code. Simply put I cannot follow it.

In a year after this project you may need to come back to modify your code. The problem is you won't know what Command7 or Current3 or List39 are.

You need better names. Do not accept the Default names.

You asked about the 3rd Combo going to the right record. Your Form does not display any data so what do you mean by this.