Field value to determine which subform opens 2

[dpav29]

I posted this earlier in the week thread181-229490 but I still need help. I'm going to try to explain it a different way:

I have a data entry form in which users post review data. Two of the fields need to cause some other fields to "auto populate". I've tried to set it up using sub forms as so:

Form1=Main data entry form, relevant fields are "date" and "item".
subform1=2002 price list
subform2=2003 price list

Here's what I'm trying to accomplish. . . the user enters a date, and then an item. I want the current item price and description to display - it will be based on one of two subforms based on the date (i.e. if the date is 2/1/02, it would display info from subform 1, if date is 7/1/02, it would display info from subform 2.

When the form is completely filled in, all data will be saved in the order table.

I've tried to write event procedure with IIF statement (i.e. IIF date<7/1/02 THEN subform1, etc.), but it's apparently way over my head!

It was suggested that I combine the two &quot;price list&quot; tables and add an effective date field. Suffice to say I can't do that. . .it's not as simple as I've depicted here.

Thanks!!

 

[SyedAqif]

Hi

Can't u write something behind On Exit event of date

Dim ChkDate as date
Dim ItPrice as integer

If Forms!MainForm!date < '07/01/02' Then
ItPrice=Forms!MainForm!SubForm1!Price
Forms!MainForm!Price=itPrice
Else
ItPrice=Forms!MainForm!SubForm2!Price
Forms!MainForm!Price=itPrice

End If


I hope it will give u some insight into the thng which I am getting at. If i have ur exact database then maybe I can help in a better way.

Cheers!
Aqif

 

[dpav29]

Thanks Aqif. . .I saw in your profile that you're into Medical related databases. So am I! When I get back to my home-office, I'll send you the database in question if you'd be willing to look at it for me to help with this issue. If so, give me your email address. Thanks!

 

[Bry12345]

If I understand you right, you are trying to sort of change forms &quot;on-the-fly&quot;. Maybe instead of opening your detailed form immediately, obtain the date and item number in either dialog boxes (or a small form) and then open the appropriate form/subform based up If logic?

 

[dpav29]

That is an outstanding idea! I think I'll play with that a while and see how it looks/works. Thanks a million!

 

[dpav29]

Bry12345,
Can you help me out a little with the IIF logic?

I'm trying your suggestion. . .a small form with a couple fields to start a new case. I thought then I'd have a macro (button) to &quot;create new record&quot;. This would A) open the main form with the appropriate subform based on the date in the starting form and B) populate the main form with the data entered into the start form.

Problem is, I'm not very confident that I'm headed in the right direction. . . I'm pretty &quot;green&quot; when going outside basic macro/expression building wizards!

Thanks for your time!

 

[Bry12345]

I'll try . . let me make sure I fully understand what you are trying to do. Before the form loads, the user enters a date and an item number. Depending on the date entered, one of two forms (which contain subforms) will load. Regardless of which form loads, the form will display (presumably in the main form) the item number and related data. Good so far?

Regarding the date that the user enters up front, does this date need to be entered anywhere on the resulting form, or is it only used to decide which form to open?

 

[dpav29]

Stand by. . I think I got it!