Different default value in a subform

[naungsai]

Dear Friends

Merry Christmas and Happy New Year!
I am building a form with subform.
I want to set different default value in the subform. The value will depend on a cell value in the form.

For example:
If the value of "Category" in the form is "1", the default value in the subform would be-
Description Unit Amount
1. Beer Bottle 3
2. PotatoChip Pack 1

If the value of "Category" in the form is "2", the default value in the subform would be-
Description Unit Amount
1. Beer Bottle 5
2. PotatoChip Pack 2

If the value of "Category" in the form is "3", the default value in the subform would be-
Description Unit Amount
1. Beer Bottle 10
2. PotatoChip Pack 5


Hope somebody help me.
Thanks in advanced.

Sai Kyaw Han

 

[John Big Booty]

You could put some code like the following in field Category's After Update event that looks something like;

If Me.Category = 1 Then
     	Me!SubformName.Form!ControlNameBeer.DefaultValue = 3
     	Me!SubformName.Form!ControlNamePotatoChip .DefaultValue = 1
Else If Me.Category = 2 Then
     	Me!SubformName.Form!ControlNameBeer.DefaultValue = 5
     	Me!SubformName.Form!ControlNamePotatoChip .DefaultValue = 2
Else
     	Me!SubformName.Form!ControlNameBeer.DefaultValue = 10
     	Me!SubformName.Form!ControlNamePotatoChip .DefaultValue = 5
End If

Bookmark this link for future reference on how to reference sub form's and their controls.

 

[SpentGeezer]

I am going to eat / drink catergory 3 tonight at the Christmas Party!!

 

[John Big Booty]

I am going to eat / drink catergory 3 tonight at the Christmas Party!!

In moderation I trust

 

[vbaInet]

In moderation I trust

I doubt that. That's the category with the most quantity of beer and chips.

 

[John Big Booty]

I doubt that. That's the category with the most quantity of beer and chips.

... and the most fun at the time

But perhaps the least fun latter on though

 

[vbaInet]

Perhaps the least fun latter on though

Most definately. But we still do it again regardless!

 

[John Big Booty]

Most definately. But we still do it again regardless!

Hell yeh

 

[naungsai]

You could put some code like the following in field Category's After Update event that looks something like;

If Me.Category = 1 Then
     	Me!SubformName.Form!ControlNameBeer.DefaultValue = 3
     	Me!SubformName.Form!ControlNamePotatoChip .DefaultValue = 1
Else If Me.Category = 2 Then
     	Me!SubformName.Form!ControlNameBeer.DefaultValue = 5
     	Me!SubformName.Form!ControlNamePotatoChip .DefaultValue = 2
Else
     	Me!SubformName.Form!ControlNameBeer.DefaultValue = 10
     	Me!SubformName.Form!ControlNamePotatoChip .DefaultValue = 5
End If

Bookmark this link for future reference on how to reference sub form's and their controls.

Dear John
thank you very much for your quick reply. I would like to clarify my question more.

ID	Food	Unit	Amount	Regieme
1	Beer	Bottle	1	1
2	Potato	Pack	2	1
4	Beer	Bottle	2	2
5	Potato	Pack	3	2
6	BBQ	Plate	3	2
7	Beer	Bottle	5	3
8	Potato	Pack	10	3
9	BBQ	Plate	10	3
10	Water	Bottle	5	3

If I choose the Regime 1 in the form, the subform which is a table, will be filled with 2 value from the above list.
Accordingly, if I choose the other 2 or 3, the list will be fill in the subform table after updating the value in the Regime.

Does my explanation clear? Hope you could help me find the solution.

Happy new year.
Sai

 

[Rabbie]

... and the most fun at the time

But perhaps the least fun latter on though

So there is some justice What goes around comes around

 

[Galaxiom]

The subform Source Object should not be a table but a form based on the table.

I am not sure the default value is what you really need. The default value is only applied on the creation of a new record. I suspect you might need to write the value directly to the control for the current record of the subform.

With so many different ID values you would use Select Case rather than the series of IF statements.

 

[naungsai]

The subform I have created is based on the table. I had set the property of subform in "Continuous Forms".
In this subform, I want to fill the default list.

 

[vbaInet]

If the value of "Category" in the form is "3", the default value in the subform would be-
Description Unit Amount
1. Beer Bottle 10
2. PotatoChip Pack 5

ID    Food    Unit    Amount    [COLOR=Navy][B]Regieme[/B][/COLOR]
[B][COLOR=Red]1    Beer    Bottle    1    [COLOR=Navy]1[/COLOR]
2    Potato    Pack    2    [COLOR=Navy]1[/COLOR][/COLOR][/B]
4    Beer    Bottle    2    2
5    Potato    Pack    3    2
6    BBQ    Plate    3    2
7    Beer    Bottle    5    3
8    Potato    Pack    10    3
9    BBQ    Plate    10    3
10    Water    Bottle    5    3

If I choose the Regime 1 in the form, the subform which is a table, will be filled with 2 value from the above list.

Ok, it looks like this isn't a Default Values issue. The OP wants to create new records in the subform based the Regime selected on the main form.

This is bad practice and should not be considered as all you will be doing is duplicating data.

 

[naungsai]

Yes. It is right. Instead of default value, I want to add new record.

Thanks in deed for your comment not to do it.