Combination box

[Ticotion]

Hi

I have a simple form coding problem.

I have a combination box that contains week numbers. Furthermore I have a
field for produced amount. When a user selects a weeknumber I want the form
to find the value in the datasource tabel, so that the input in the produced
amount field are saved here. Can you help me?

Thank you for your help and input
Ticotion

 

[Evi]

It might help you to give details such as name of combo box, name of table,
name of field for Produced Amount. it will make it easier for you to read
the code too. It also saves us having to assign names to them.

Since this is a simple coding problem, I assume you know how to use events.

So the code in the After Update Event for the combo will be something like
this

Me.ProducedAmount = (DLookup("[LookedUpValue]",
"DatasourceTable2","[WeekNumber]=" & Me.MyCombosName)

You'll need to put in the real names

You will need to decide what to do if there is nothing listed for that
WeekNumber in your datasource table - look up the NZ function for instance.
Evi

 

[Ticotion]

Hi Evi

Name of combo boks is CBO2 which looks up week values in tbl_320.

The name of the field Produced amount is Monday. I also have a field
tuesday, wedensday and so on, where the same thing should happen.

When a user inputs numbers in the "Monday" field or Tuesday field and so on,
the user should by pressing a button save the input values for the week
choosen, in tbl_320.

If nothing is listed then nothing should be written in the table.

I've tried to use your code but can't realyy get it to work.

Thanks for your help
Ticotion

It might help you to give details such as name of combo box, name of table,
name of field for Produced Amount. it will make it easier for you to read
the code too. It also saves us having to assign names to them.

Since this is a simple coding problem, I assume you know how to use events.

So the code in the After Update Event for the combo will be something like
this

Me.ProducedAmount = (DLookup("[LookedUpValue]",
"DatasourceTable2","[WeekNumber]=" & Me.MyCombosName)

You'll need to put in the real names

You will need to decide what to do if there is nothing listed for that
WeekNumber in your datasource table - look up the NZ function for instance.
Evi

Hi

I have a simple form coding problem.

I have a combination box that contains week numbers. Furthermore I have a
field for produced amount. When a user selects a weeknumber I want the form
to find the value in the datasource tabel, so that the input in the produced
amount field are saved here. Can you help me?

Thank you for your help and input
Ticotion

 

[Evi]

This still isn't making sense to me.

'The name of the field Produced amount is Monday (eh???). I also have a
field
tuesday, wedensday and so on, where the same thing should happen'.

Are you saying that you have Fields in your table called Monday, Tuesday
Wednesday etc?
(if yes, you may need to check your database design in the TableDesign
newsgroup)
or are you saying that you have text boxes in an unbound form which you have
named (in Properties) Monday, Tuesday, Wednesday

Does Tbl320 also contain a field which contains numbers (1, 2, 3 etc) for
each week? Or does it contain dates. Are you trying to add a new new record
to Tbl320 or edit a record already there? ie are you trying to run an
append or an update query?

Is tbl320 set out like some kind of spreadsheet with the weekdays being used
as field names and the week numbers typed down the side?

Is there a reason why you can't base a form on this table and enter the data
directly into the table via the form which you can filter using a combo?

Or do you want your form to total the text boxes Monday, Tuesday Wednesday
because this is an Unbound text box and enter the results into your Tbl320
in a field called ProducedAmount next to the Week field with the chosen
number

I think there may be something you have missed out of your explanation.
Evi

Hi Evi

Name of combo boks is CBO2 which looks up week values in tbl_320.

The name of the field Produced amount is Monday. I also have a field
tuesday, wedensday and so on, where the same thing should happen.

When a user inputs numbers in the "Monday" field or Tuesday field and so on,
the user should by pressing a button save the input values for the week
choosen, in tbl_320.

If nothing is listed then nothing should be written in the table.

I've tried to use your code but can't realyy get it to work.

Thanks for your help
Ticotion

It might help you to give details such as name of combo box, name of table,
name of field for Produced Amount. it will make it easier for you to read
the code too. It also saves us having to assign names to them.

Since this is a simple coding problem, I assume you know how to use events.

So the code in the After Update Event for the combo will be something like
this

Me.ProducedAmount = (DLookup("[LookedUpValue]",
"DatasourceTable2","[WeekNumber]=" & Me.MyCombosName)

You'll need to put in the real names

You will need to decide what to do if there is nothing listed for that
WeekNumber in your datasource table - look up the NZ function for instance.
Evi

Hi

I have a simple form coding problem.

I have a combination box that contains week numbers. Furthermore I have a
field for produced amount. When a user selects a weeknumber I want the form
to find the value in the datasource tabel, so that the input in the produced
amount field are saved here. Can you help me?

Thank you for your help and input
Ticotion

 

[Ticotion]

Hi Evi

It is proberbly very simple. I will try to explain it in another way.

My main tabel is called tbl_320 and looks like the following:

ID year week monday tuesday wedensday
1 2008 1
2 2008 2

I have a combo box call year and a combo box called week getting data from
tbl_320.

Then I have a box called monday, one called tuesday and so on relating to
each colounm in tbl_320.

The user should input produced amount in each box based on the selection
criteria year and week.

So If a user choose year = 2008 and week = 21 the monday, tuesday boxes will
show the input values for those fields (if any) or the user can input new
values.

Hope this is a better explanation

Thanks again for your help

Ticotion

This still isn't making sense to me.

'The name of the field Produced amount is Monday (eh???). I also have a
field
tuesday, wedensday and so on, where the same thing should happen'.

Are you saying that you have Fields in your table called Monday, Tuesday
Wednesday etc?
(if yes, you may need to check your database design in the TableDesign
newsgroup)
or are you saying that you have text boxes in an unbound form which you have
named (in Properties) Monday, Tuesday, Wednesday

Does Tbl320 also contain a field which contains numbers (1, 2, 3 etc) for
each week? Or does it contain dates. Are you trying to add a new new record
to Tbl320 or edit a record already there? ie are you trying to run an
append or an update query?

Is tbl320 set out like some kind of spreadsheet with the weekdays being used
as field names and the week numbers typed down the side?

Is there a reason why you can't base a form on this table and enter the data
directly into the table via the form which you can filter using a combo?

Or do you want your form to total the text boxes Monday, Tuesday Wednesday
because this is an Unbound text box and enter the results into your Tbl320
in a field called ProducedAmount next to the Week field with the chosen
number

I think there may be something you have missed out of your explanation.
Evi

Hi Evi

Name of combo boks is CBO2 which looks up week values in tbl_320.

The name of the field Produced amount is Monday. I also have a field
tuesday, wedensday and so on, where the same thing should happen.

When a user inputs numbers in the "Monday" field or Tuesday field and so on,
the user should by pressing a button save the input values for the week
choosen, in tbl_320.

If nothing is listed then nothing should be written in the table.

I've tried to use your code but can't realyy get it to work.

Thanks for your help
Ticotion

It might help you to give details such as name of combo box, name of table,
name of field for Produced Amount. it will make it easier for you to read
the code too. It also saves us having to assign names to them.

Since this is a simple coding problem, I assume you know how to use events.

So the code in the After Update Event for the combo will be something like
this

Me.ProducedAmount = (DLookup("[LookedUpValue]",
"DatasourceTable2","[WeekNumber]=" & Me.MyCombosName)

You'll need to put in the real names

You will need to decide what to do if there is nothing listed for that
WeekNumber in your datasource table - look up the NZ function for instance.
Evi

Hi

I have a simple form coding problem.

I have a combination box that contains week numbers. Furthermore I have a
field for produced amount. When a user selects a weeknumber I want the
form
to find the value in the datasource tabel, so that the input in the
produced
amount field are saved here. Can you help me?

Thank you for your help and input
Ticotion

 

[Douglas J. Steele]

This likely isn't what you want to hear, but your table is not properly
designed. Having fields named Monday, Tuesday, Wednesday indicates that your
table hasn't been normalized. Those are known as a repeating group, and, as
you're discovering, they make dealing with the data much harder. (Not only
that, but you're essentially hiding data in the name of the field).

Your table should have one row for each day:

ID yearnb weeknb dayname
1 2008 1 monday
1 2008 1 tuesday
1 2008 1 wednesday
2 2008 2 monday
2 2008 2 tuesday
2 2008 2 wednesday

Note too that I renamed your fields. Year and Week are both reserved words,
and you should never use reserved words for your own purposes. For a
comprehensive list of names to avoid, see what Allen Browne has at
http://www.allenbrowne.com/AppIssueBadWord.html

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

Hi Evi

It is proberbly very simple. I will try to explain it in another way.

My main tabel is called tbl_320 and looks like the following:

ID year week monday tuesday wedensday
1 2008 1
2 2008 2

I have a combo box call year and a combo box called week getting data from
tbl_320.

Then I have a box called monday, one called tuesday and so on relating to
each colounm in tbl_320.

The user should input produced amount in each box based on the selection
criteria year and week.

So If a user choose year = 2008 and week = 21 the monday, tuesday boxes
will
show the input values for those fields (if any) or the user can input new
values.

Hope this is a better explanation

Thanks again for your help

Ticotion

This still isn't making sense to me.

'The name of the field Produced amount is Monday (eh???). I also have a
field
tuesday, wedensday and so on, where the same thing should happen'.

Are you saying that you have Fields in your table called Monday, Tuesday
Wednesday etc?
(if yes, you may need to check your database design in the TableDesign
newsgroup)
or are you saying that you have text boxes in an unbound form which you
have
named (in Properties) Monday, Tuesday, Wednesday

Does Tbl320 also contain a field which contains numbers (1, 2, 3 etc)
for
each week? Or does it contain dates. Are you trying to add a new new
record
to Tbl320 or edit a record already there? ie are you trying to run an
append or an update query?

Is tbl320 set out like some kind of spreadsheet with the weekdays being
used
as field names and the week numbers typed down the side?

Is there a reason why you can't base a form on this table and enter the
data
directly into the table via the form which you can filter using a combo?

Or do you want your form to total the text boxes Monday, Tuesday
Wednesday
because this is an Unbound text box and enter the results into your
Tbl320
in a field called ProducedAmount next to the Week field with the chosen
number

I think there may be something you have missed out of your explanation.
Evi

Hi Evi

Name of combo boks is CBO2 which looks up week values in tbl_320.

The name of the field Produced amount is Monday. I also have a field
tuesday, wedensday and so on, where the same thing should happen.

When a user inputs numbers in the "Monday" field or Tuesday field and
so on,
the user should by pressing a button save the input values for the week
choosen, in tbl_320.

If nothing is listed then nothing should be written in the table.

I've tried to use your code but can't realyy get it to work.

Thanks for your help
Ticotion

:

It might help you to give details such as name of combo box, name of table,
name of field for Produced Amount. it will make it easier for you to read
the code too. It also saves us having to assign names to them.

Since this is a simple coding problem, I assume you know how to use events.

So the code in the After Update Event for the combo will be something like
this

Me.ProducedAmount = (DLookup("[LookedUpValue]",
"DatasourceTable2","[WeekNumber]=" & Me.MyCombosName)

You'll need to put in the real names

You will need to decide what to do if there is nothing listed for
that
WeekNumber in your datasource table - look up the NZ function for instance.
Evi

Hi

I have a simple form coding problem.

I have a combination box that contains week numbers. Furthermore I have a
field for produced amount. When a user selects a weeknumber I want
the
form
to find the value in the datasource tabel, so that the input in the
produced
amount field are saved here. Can you help me?

Thank you for your help and input
Ticotion

 

[Evi]

Leaving aside your non-normalised database (see Douglas's post)

Why don't you just base your form on your Tbl320 then your combo box can
filter it to show the required row. Why go to all the trouble of using an
update or append query?

You say
So If a user choose year = 2008 and week = 21

So are you now saying that your combo have 2 columns, one for the week
number and one for the year

Or are you saying that you have 2 combos, one for the week number, one for
the year.
(I'd have thought this second design would be the better)

You could filter a continuous form with these combos with code like this in
the AfterUpdate event of the second combo

If IsNull(Me.Combo1) then
'check if the user has entered a year
MsgBox "Please choose a year"
Else
Me.Filter = "[Weeknb]=" & Me.Combo1 & " AND [Yearnb] = " & Me.Combo2
Me.FilterOn = True
End If

or if have you put your table into an unbound form as a subform and you want
to filter the subform:

If IsNull(Me.Combo1) then
'check if the user has entered a year
MsgBox "Please choose a year"
Else
Me.YourSubformName.Form.Filter = "[Weeknb]=" & Me.Combo1 & " AND [Yearnb] =
" & Me.Combo2
Me.YourSubformName.Form.FilterOn = True
End If



Evi

Hi Evi

It is proberbly very simple. I will try to explain it in another way.

My main tabel is called tbl_320 and looks like the following:

ID year week monday tuesday wedensday
1 2008 1
2 2008 2

I have a combo box call year and a combo box called week getting data from
tbl_320.

Then I have a box called monday, one called tuesday and so on relating to
each colounm in tbl_320.

The user should input produced amount in each box based on the selection
criteria year and week.

So If a user choose year = 2008 and week = 21 the monday, tuesday boxes will
show the input values for those fields (if any) or the user can input new
values.

Hope this is a better explanation

Thanks again for your help

Ticotion

This still isn't making sense to me.

'The name of the field Produced amount is Monday (eh???). I also have a
field
tuesday, wedensday and so on, where the same thing should happen'.

Are you saying that you have Fields in your table called Monday, Tuesday
Wednesday etc?
(if yes, you may need to check your database design in the TableDesign
newsgroup)
or are you saying that you have text boxes in an unbound form which you have
named (in Properties) Monday, Tuesday, Wednesday

Does Tbl320 also contain a field which contains numbers (1, 2, 3 etc) for
each week? Or does it contain dates. Are you trying to add a new new record
to Tbl320 or edit a record already there? ie are you trying to run an
append or an update query?

Is tbl320 set out like some kind of spreadsheet with the weekdays being used
as field names and the week numbers typed down the side?

Is there a reason why you can't base a form on this table and enter the data
directly into the table via the form which you can filter using a combo?

Or do you want your form to total the text boxes Monday, Tuesday Wednesday
because this is an Unbound text box and enter the results into your Tbl320
in a field called ProducedAmount next to the Week field with the chosen
number

I think there may be something you have missed out of your explanation.
Evi

Hi Evi

Name of combo boks is CBO2 which looks up week values in tbl_320.

The name of the field Produced amount is Monday. I also have a field
tuesday, wedensday and so on, where the same thing should happen.

When a user inputs numbers in the "Monday" field or Tuesday field and

so
on,

the user should by pressing a button save the input values for the week
choosen, in tbl_320.

If nothing is listed then nothing should be written in the table.

I've tried to use your code but can't realyy get it to work.

Thanks for your help
Ticotion

:

It might help you to give details such as name of combo box, name of table,
name of field for Produced Amount. it will make it easier for you to read
the code too. It also saves us having to assign names to them.

Since this is a simple coding problem, I assume you know how to use events.

So the code in the After Update Event for the combo will be

something
like

this

Me.ProducedAmount = (DLookup("[LookedUpValue]",
"DatasourceTable2","[WeekNumber]=" & Me.MyCombosName)

You'll need to put in the real names

You will need to decide what to do if there is nothing listed for that
WeekNumber in your datasource table - look up the NZ function for instance.
Evi

Hi

I have a simple form coding problem.

I have a combination box that contains week numbers. Furthermore I have a
field for produced amount. When a user selects a weeknumber I want the
form
to find the value in the datasource tabel, so that the input in the
produced
amount field are saved here. Can you help me?

Thank you for your help and input
Ticotion

 

[Ticotion]

Hi

That was the Hint that I needed. Thank you very much

One more question. I want to filter a report with combo boxes.

Combo box1 = From year
Combo box2 = From Week

Combo box3 = To Year
Combo box4 = To Week

I've placed these comboxes on a form and then used the following on the
click event

DoCmd.OpenReport "Rpt_320fuga", acPreview, , "[Year] = " & Me.Combo7 & " AND
[Week] = " & Me.Combo5 & " and "[Year] = " & Me.Combo8 & " AND [Week] = " &
Me.Combo9

This doesn't work. Can U help me

Thanks
Ticotion

Leaving aside your non-normalised database (see Douglas's post)

Why don't you just base your form on your Tbl320 then your combo box can
filter it to show the required row. Why go to all the trouble of using an
update or append query?

You say
So If a user choose year = 2008 and week = 21

So are you now saying that your combo have 2 columns, one for the week
number and one for the year

Or are you saying that you have 2 combos, one for the week number, one for
the year.
(I'd have thought this second design would be the better)

You could filter a continuous form with these combos with code like this in
the AfterUpdate event of the second combo

If IsNull(Me.Combo1) then
'check if the user has entered a year
MsgBox "Please choose a year"
Else
Me.Filter = "[Weeknb]=" & Me.Combo1 & " AND [Yearnb] = " & Me.Combo2
Me.FilterOn = True
End If

or if have you put your table into an unbound form as a subform and you want
to filter the subform:

If IsNull(Me.Combo1) then
'check if the user has entered a year
MsgBox "Please choose a year"
Else
Me.YourSubformName.Form.Filter = "[Weeknb]=" & Me.Combo1 & " AND [Yearnb] =
" & Me.Combo2
Me.YourSubformName.Form.FilterOn = True
End If



Evi

Hi Evi

It is proberbly very simple. I will try to explain it in another way.

My main tabel is called tbl_320 and looks like the following:

ID year week monday tuesday wedensday
1 2008 1
2 2008 2

I have a combo box call year and a combo box called week getting data from
tbl_320.

Then I have a box called monday, one called tuesday and so on relating to
each colounm in tbl_320.

The user should input produced amount in each box based on the selection
criteria year and week.

So If a user choose year = 2008 and week = 21 the monday, tuesday boxes will
show the input values for those fields (if any) or the user can input new
values.

Hope this is a better explanation

Thanks again for your help

Ticotion

This still isn't making sense to me.

'The name of the field Produced amount is Monday (eh???). I also have a
field
tuesday, wedensday and so on, where the same thing should happen'.

Are you saying that you have Fields in your table called Monday, Tuesday
Wednesday etc?
(if yes, you may need to check your database design in the TableDesign
newsgroup)
or are you saying that you have text boxes in an unbound form which you have
named (in Properties) Monday, Tuesday, Wednesday

Does Tbl320 also contain a field which contains numbers (1, 2, 3 etc) for
each week? Or does it contain dates. Are you trying to add a new new record
to Tbl320 or edit a record already there? ie are you trying to run an
append or an update query?

Is tbl320 set out like some kind of spreadsheet with the weekdays being used
as field names and the week numbers typed down the side?

Is there a reason why you can't base a form on this table and enter the data
directly into the table via the form which you can filter using a combo?

Or do you want your form to total the text boxes Monday, Tuesday Wednesday
because this is an Unbound text box and enter the results into your Tbl320
in a field called ProducedAmount next to the Week field with the chosen
number

I think there may be something you have missed out of your explanation.
Evi










Hi Evi

Name of combo boks is CBO2 which looks up week values in tbl_320.

The name of the field Produced amount is Monday. I also have a field
tuesday, wedensday and so on, where the same thing should happen.

When a user inputs numbers in the "Monday" field or Tuesday field and so
on,
the user should by pressing a button save the input values for the week
choosen, in tbl_320.

If nothing is listed then nothing should be written in the table.

I've tried to use your code but can't realyy get it to work.

Thanks for your help
Ticotion

:

It might help you to give details such as name of combo box, name of
table,
name of field for Produced Amount. it will make it easier for you to
read
the code too. It also saves us having to assign names to them.

Since this is a simple coding problem, I assume you know how to use
events.

So the code in the After Update Event for the combo will be something
like
this

Me.ProducedAmount = (DLookup("[LookedUpValue]",
"DatasourceTable2","[WeekNumber]=" & Me.MyCombosName)

You'll need to put in the real names

You will need to decide what to do if there is nothing listed for that
WeekNumber in your datasource table - look up the NZ function for
instance.
Evi

Hi

I have a simple form coding problem.

I have a combination box that contains week numbers. Furthermore I
have a
field for produced amount. When a user selects a weeknumber I want the
form
to find the value in the datasource tabel, so that the input in the
produced
amount field are saved here. Can you help me?

Thank you for your help and input
Ticotion

 

[Douglas J. Steele]

You've got a double quote that's incorrect:

& Me.Combo5 & " and "[Year] = "

should be

& Me.Combo5 & " and [Year] = "

However, correcting that is likely not going to give you what you want.

Your boolean logic is faulty. The condition is going to report every row for
which the year is equal to the value in Combo7 and the value in Combo8, and
the week is equal to the value in Combo5 and the value in Combo9. Since a
field can only have a single value, it's unlikely that any rows will be
returned (unless Combo7 and Combo8 both have the same value selected and
Combo5 and Combo9 both have the same value selected).

You could try something like:

DoCmd.OpenReport "Rpt_320fuga", acPreview, , "[Year] >= " & Me.Combo7 & "
AND
[Week] >= " & Me.Combo5 & " and [Year] <= " & Me.Combo8 & " AND [Week] <= "
&
Me.Combo9

However, that will only work if Combo7 and Combo8 both have the same value
selected. Otherwise, you'll end up with something like:

DoCmd.OpenReport "Rpt_320fuga", acPreview, , "[Year] >= 2007 AND [Week] >=
52 and [Year] <= 2008 AND [Week] <= 1"

which again will likely return nothing.

Try:

DoCmd.OpenReport "Rpt_320fuga", acPreview, , "[Year] & ""-"" &
Format([Week], ""00"") >= " & Me.Combo7 & " -" & Format(Me.Combo5, 00) & "
and [Year] & ""-"" & Format([Week], ""00"") <= " & Me.Combo8 & "-" &
Format(Me.Combo9, "00")

You need the Format functions there to ensure that you have 01, 02, 03, ...
not 1, 2, 3.

I'll repeat my advice that even if you're not going to correct the table
design, you should still rename your fields.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Hi

That was the Hint that I needed. Thank you very much

One more question. I want to filter a report with combo boxes.

Combo box1 = From year
Combo box2 = From Week

Combo box3 = To Year
Combo box4 = To Week

I've placed these comboxes on a form and then used the following on the
click event

DoCmd.OpenReport "Rpt_320fuga", acPreview, , "[Year] = " & Me.Combo7 & "
AND
[Week] = " & Me.Combo5 & " and "[Year] = " & Me.Combo8 & " AND [Week] = "
&
Me.Combo9

This doesn't work. Can U help me

Thanks
Ticotion

Leaving aside your non-normalised database (see Douglas's post)

Why don't you just base your form on your Tbl320 then your combo box can
filter it to show the required row. Why go to all the trouble of using an
update or append query?

You say
So If a user choose year = 2008 and week = 21

So are you now saying that your combo have 2 columns, one for the week
number and one for the year

Or are you saying that you have 2 combos, one for the week number, one
for
the year.
(I'd have thought this second design would be the better)

You could filter a continuous form with these combos with code like this
in
the AfterUpdate event of the second combo

If IsNull(Me.Combo1) then
'check if the user has entered a year
MsgBox "Please choose a year"
Else
Me.Filter = "[Weeknb]=" & Me.Combo1 & " AND [Yearnb] = " & Me.Combo2
Me.FilterOn = True
End If

or if have you put your table into an unbound form as a subform and you
want
to filter the subform:

If IsNull(Me.Combo1) then
'check if the user has entered a year
MsgBox "Please choose a year"
Else
Me.YourSubformName.Form.Filter = "[Weeknb]=" & Me.Combo1 & " AND [Yearnb]
=
" & Me.Combo2
Me.YourSubformName.Form.FilterOn = True
End If



Evi

Hi Evi

It is proberbly very simple. I will try to explain it in another way.

My main tabel is called tbl_320 and looks like the following:

ID year week monday tuesday wedensday
1 2008 1
2 2008 2

I have a combo box call year and a combo box called week getting data
from
tbl_320.

Then I have a box called monday, one called tuesday and so on relating
to
each colounm in tbl_320.

The user should input produced amount in each box based on the
selection
criteria year and week.

So If a user choose year = 2008 and week = 21 the monday, tuesday boxes will
show the input values for those fields (if any) or the user can input
new
values.

Hope this is a better explanation

Thanks again for your help

Ticotion

:



This still isn't making sense to me.

'The name of the field Produced amount is Monday (eh???). I also have
a
field
tuesday, wedensday and so on, where the same thing should happen'.

Are you saying that you have Fields in your table called Monday,
Tuesday
Wednesday etc?
(if yes, you may need to check your database design in the
TableDesign
newsgroup)
or are you saying that you have text boxes in an unbound form which
you have
named (in Properties) Monday, Tuesday, Wednesday

Does Tbl320 also contain a field which contains numbers (1, 2, 3 etc) for
each week? Or does it contain dates. Are you trying to add a new new record
to Tbl320 or edit a record already there? ie are you trying to run
an
append or an update query?

Is tbl320 set out like some kind of spreadsheet with the weekdays
being used
as field names and the week numbers typed down the side?

Is there a reason why you can't base a form on this table and enter
the data
directly into the table via the form which you can filter using a
combo?

Or do you want your form to total the text boxes Monday, Tuesday Wednesday
because this is an Unbound text box and enter the results into your Tbl320
in a field called ProducedAmount next to the Week field with the
chosen
number

I think there may be something you have missed out of your
explanation.
Evi










Hi Evi

Name of combo boks is CBO2 which looks up week values in tbl_320.

The name of the field Produced amount is Monday. I also have a
field
tuesday, wedensday and so on, where the same thing should happen.

When a user inputs numbers in the "Monday" field or Tuesday field
and so
on,
the user should by pressing a button save the input values for the week
choosen, in tbl_320.

If nothing is listed then nothing should be written in the table.

I've tried to use your code but can't realyy get it to work.

Thanks for your help
Ticotion

:

It might help you to give details such as name of combo box, name
of
table,
name of field for Produced Amount. it will make it easier for you
to
read
the code too. It also saves us having to assign names to them.

Since this is a simple coding problem, I assume you know how to
use
events.

So the code in the After Update Event for the combo will be something
like
this

Me.ProducedAmount = (DLookup("[LookedUpValue]",
"DatasourceTable2","[WeekNumber]=" & Me.MyCombosName)

You'll need to put in the real names

You will need to decide what to do if there is nothing listed for that
WeekNumber in your datasource table - look up the NZ function for
instance.
Evi

Hi

I have a simple form coding problem.

I have a combination box that contains week numbers.
Furthermore I
have a
field for produced amount. When a user selects a weeknumber I
want the
form
to find the value in the datasource tabel, so that the input in the
produced
amount field are saved here. Can you help me?

Thank you for your help and input
Ticotion

 

[Evi]

Lets say that Combo7 has 2007 and Combo8 has 2008 Your code is saying. Open
the report where the year field = 2007 and also = 2008.
Try this:
DoCmd.OpenReport "Rpt_320fuga", acPreview, , "[Year] >= " & Me.Combo7 & "
AND
[Week] >= " & Me.Combo5 & " AND [Year] <= " & Me.Combo8 & " AND [Week] <= "
&
Me.Combo9

Hi

That was the Hint that I needed. Thank you very much

One more question. I want to filter a report with combo boxes.

Combo box1 = From year
Combo box2 = From Week

Combo box3 = To Year
Combo box4 = To Week

I've placed these comboxes on a form and then used the following on the
click event

DoCmd.OpenReport "Rpt_320fuga", acPreview, , "[Year] = " & Me.Combo7 & " AND
[Week] = " & Me.Combo5 & " and "[Year] = " & Me.Combo8 & " AND [Week] = " &
Me.Combo9

This doesn't work. Can U help me

Thanks
Ticotion

Leaving aside your non-normalised database (see Douglas's post)

Why don't you just base your form on your Tbl320 then your combo box can
filter it to show the required row. Why go to all the trouble of using an
update or append query?

You say
So If a user choose year = 2008 and week = 21

So are you now saying that your combo have 2 columns, one for the week
number and one for the year

Or are you saying that you have 2 combos, one for the week number, one for
the year.
(I'd have thought this second design would be the better)

You could filter a continuous form with these combos with code like this in
the AfterUpdate event of the second combo

If IsNull(Me.Combo1) then
'check if the user has entered a year
MsgBox "Please choose a year"
Else
Me.Filter = "[Weeknb]=" & Me.Combo1 & " AND [Yearnb] = " & Me.Combo2
Me.FilterOn = True
End If

or if have you put your table into an unbound form as a subform and you want
to filter the subform:

If IsNull(Me.Combo1) then
'check if the user has entered a year
MsgBox "Please choose a year"
Else
Me.YourSubformName.Form.Filter = "[Weeknb]=" & Me.Combo1 & " AND [Yearnb] =
" & Me.Combo2
Me.YourSubformName.Form.FilterOn = True
End If



Evi

Hi Evi

It is proberbly very simple. I will try to explain it in another way.

My main tabel is called tbl_320 and looks like the following:

ID year week monday tuesday wedensday
1 2008 1
2 2008 2

I have a combo box call year and a combo box called week getting data from
tbl_320.

Then I have a box called monday, one called tuesday and so on relating to
each colounm in tbl_320.

The user should input produced amount in each box based on the selection
criteria year and week.

So If a user choose year = 2008 and week = 21 the monday, tuesday

boxes
will

show the input values for those fields (if any) or the user can input new
values.

Hope this is a better explanation

Thanks again for your help

Ticotion

:



This still isn't making sense to me.

'The name of the field Produced amount is Monday (eh???). I also have a
field
tuesday, wedensday and so on, where the same thing should happen'.

Are you saying that you have Fields in your table called Monday, Tuesday
Wednesday etc?
(if yes, you may need to check your database design in the TableDesign
newsgroup)
or are you saying that you have text boxes in an unbound form which

you
have

named (in Properties) Monday, Tuesday, Wednesday

Does Tbl320 also contain a field which contains numbers (1, 2, 3

etc)
for

each week? Or does it contain dates. Are you trying to add a new new record
to Tbl320 or edit a record already there? ie are you trying to run an
append or an update query?

Is tbl320 set out like some kind of spreadsheet with the weekdays

being
used

as field names and the week numbers typed down the side?

Is there a reason why you can't base a form on this table and enter

the
data

directly into the table via the form which you can filter using a combo?

Or do you want your form to total the text boxes Monday, Tuesday Wednesday
because this is an Unbound text box and enter the results into your Tbl320
in a field called ProducedAmount next to the Week field with the chosen
number

I think there may be something you have missed out of your explanation.
Evi










Hi Evi

Name of combo boks is CBO2 which looks up week values in tbl_320.

The name of the field Produced amount is Monday. I also have a field
tuesday, wedensday and so on, where the same thing should happen.

When a user inputs numbers in the "Monday" field or Tuesday field

and
so

on,
the user should by pressing a button save the input values for the week
choosen, in tbl_320.

If nothing is listed then nothing should be written in the table.

I've tried to use your code but can't realyy get it to work.

Thanks for your help
Ticotion

:

It might help you to give details such as name of combo box, name of
table,
name of field for Produced Amount. it will make it easier for you to
read
the code too. It also saves us having to assign names to them.

Since this is a simple coding problem, I assume you know how to use
events.

So the code in the After Update Event for the combo will be something
like
this

Me.ProducedAmount = (DLookup("[LookedUpValue]",
"DatasourceTable2","[WeekNumber]=" & Me.MyCombosName)

You'll need to put in the real names

You will need to decide what to do if there is nothing listed

for
that

WeekNumber in your datasource table - look up the NZ function for
instance.
Evi

Hi

I have a simple form coding problem.

I have a combination box that contains week numbers. Furthermore I
have a
field for produced amount. When a user selects a weeknumber I

want
the

form
to find the value in the datasource tabel, so that the input

in
the

produced
amount field are saved here. Can you help me?

Thank you for your help and input
Ticotion

 

[Jan Baird]

Jan Baird is out of the country until September 20. Every effort will be
made to respond to messages, but please be patient.

 

[Jan Baird]

Jan Baird is out of the country until September 20. Every effort will be
made to respond to messages, but please be patient.

 

[Ticotion]

Hi Evi

That worked also. Thanks.

One last question (I hope)

I also want to be able to filter on a text field. I use the following
statement but my guess is that there is something wrong with the last
quatation

Me.Filter = "[Week]=" & Me.combo1& " AND [Year] = " & Me.combo2" AND
[Depart] = " & Me.combo3

The department field is a text field. How should the quatation for a text
field be in the code?

Once again thnaks for your help

Ticotion

Lets say that Combo7 has 2007 and Combo8 has 2008 Your code is saying. Open
the report where the year field = 2007 and also = 2008.
Try this:
DoCmd.OpenReport "Rpt_320fuga", acPreview, , "[Year] >= " & Me.Combo7 & "
AND
[Week] >= " & Me.Combo5 & " AND [Year] <= " & Me.Combo8 & " AND [Week] <= "
&
Me.Combo9

Hi

That was the Hint that I needed. Thank you very much

One more question. I want to filter a report with combo boxes.

Combo box1 = From year
Combo box2 = From Week

Combo box3 = To Year
Combo box4 = To Week

I've placed these comboxes on a form and then used the following on the
click event

DoCmd.OpenReport "Rpt_320fuga", acPreview, , "[Year] = " & Me.Combo7 & " AND
[Week] = " & Me.Combo5 & " and "[Year] = " & Me.Combo8 & " AND [Week] = " &
Me.Combo9

This doesn't work. Can U help me

Thanks
Ticotion

Leaving aside your non-normalised database (see Douglas's post)

Why don't you just base your form on your Tbl320 then your combo box can
filter it to show the required row. Why go to all the trouble of using an
update or append query?

You say
So If a user choose year = 2008 and week = 21

So are you now saying that your combo have 2 columns, one for the week
number and one for the year

Or are you saying that you have 2 combos, one for the week number, one for
the year.
(I'd have thought this second design would be the better)

You could filter a continuous form with these combos with code like this in
the AfterUpdate event of the second combo

If IsNull(Me.Combo1) then
'check if the user has entered a year
MsgBox "Please choose a year"
Else
Me.Filter = "[Weeknb]=" & Me.Combo1 & " AND [Yearnb] = " & Me.Combo2
Me.FilterOn = True
End If

or if have you put your table into an unbound form as a subform and you want
to filter the subform:

If IsNull(Me.Combo1) then
'check if the user has entered a year
MsgBox "Please choose a year"
Else
Me.YourSubformName.Form.Filter = "[Weeknb]=" & Me.Combo1 & " AND [Yearnb] =
" & Me.Combo2
Me.YourSubformName.Form.FilterOn = True
End If



Evi



Hi Evi

It is proberbly very simple. I will try to explain it in another way.

My main tabel is called tbl_320 and looks like the following:

ID year week monday tuesday wedensday
1 2008 1
2 2008 2

I have a combo box call year and a combo box called week getting data from
tbl_320.

Then I have a box called monday, one called tuesday and so on relating to
each colounm in tbl_320.

The user should input produced amount in each box based on the selection
criteria year and week.

So If a user choose year = 2008 and week = 21 the monday, tuesday boxes
will
show the input values for those fields (if any) or the user can input new
values.

Hope this is a better explanation

Thanks again for your help

Ticotion

:



This still isn't making sense to me.

'The name of the field Produced amount is Monday (eh???). I also have a
field
tuesday, wedensday and so on, where the same thing should happen'.

Are you saying that you have Fields in your table called Monday, Tuesday
Wednesday etc?
(if yes, you may need to check your database design in the TableDesign
newsgroup)
or are you saying that you have text boxes in an unbound form which you
have
named (in Properties) Monday, Tuesday, Wednesday

Does Tbl320 also contain a field which contains numbers (1, 2, 3 etc)
for
each week? Or does it contain dates. Are you trying to add a new new
record
to Tbl320 or edit a record already there? ie are you trying to run an
append or an update query?

Is tbl320 set out like some kind of spreadsheet with the weekdays being
used
as field names and the week numbers typed down the side?

Is there a reason why you can't base a form on this table and enter the
data
directly into the table via the form which you can filter using a combo?

Or do you want your form to total the text boxes Monday, Tuesday
Wednesday
because this is an Unbound text box and enter the results into your
Tbl320
in a field called ProducedAmount next to the Week field with the chosen
number

I think there may be something you have missed out of your explanation.
Evi










Hi Evi

Name of combo boks is CBO2 which looks up week values in tbl_320.

The name of the field Produced amount is Monday. I also have a field
tuesday, wedensday and so on, where the same thing should happen.

When a user inputs numbers in the "Monday" field or Tuesday field and
so
on,
the user should by pressing a button save the input values for the
week
choosen, in tbl_320.

If nothing is listed then nothing should be written in the table.

I've tried to use your code but can't realyy get it to work.

Thanks for your help
Ticotion

:

It might help you to give details such as name of combo box, name of
table,
name of field for Produced Amount. it will make it easier for you to
read
the code too. It also saves us having to assign names to them.

Since this is a simple coding problem, I assume you know how to use
events.

So the code in the After Update Event for the combo will be
something
like
this

Me.ProducedAmount = (DLookup("[LookedUpValue]",
"DatasourceTable2","[WeekNumber]=" & Me.MyCombosName)

You'll need to put in the real names

You will need to decide what to do if there is nothing listed for
that
WeekNumber in your datasource table - look up the NZ function for
instance.
Evi

Hi

I have a simple form coding problem.

I have a combination box that contains week numbers. Furthermore I
have a
field for produced amount. When a user selects a weeknumber I want
the
form
to find the value in the datasource tabel, so that the input in
the
produced
amount field are saved here. Can you help me?

Thank you for your help and input
Ticotion

 

[Evi]

sorry about the delay. All you need is

Me.Filter = "[Week]=" & Me.combo1 & " AND [Year] = " & Me.combo2 & " AND
[Depart] = """ & Me.combo3 & """"

the end bit is 3 quote marks before the first & and 4 quotes after the last
&

I've added another & before the " AND

& that should do it

Evi

Hi Evi

That worked also. Thanks.

One last question (I hope)

I also want to be able to filter on a text field. I use the following
statement but my guess is that there is something wrong with the last
quatation

Me.Filter = "[Week]=" & Me.combo1 & " AND [Year] = " & Me.combo2" AND
[Depart] = " & ' & Me.combo3

The department field is a text field. How should the quatation for a text
field be in the code?

Once again thnaks for your help

Ticotion

Lets say that Combo7 has 2007 and Combo8 has 2008 Your code is saying. Open
the report where the year field = 2007 and also = 2008.
Try this:
DoCmd.OpenReport "Rpt_320fuga", acPreview, , "[Year] >= " & Me.Combo7 & "
AND
[Week] >= " & Me.Combo5 & " AND [Year] <= " & Me.Combo8 & " AND [Week] <= "
&
Me.Combo9

Hi

That was the Hint that I needed. Thank you very much

One more question. I want to filter a report with combo boxes.

Combo box1 = From year
Combo box2 = From Week

Combo box3 = To Year
Combo box4 = To Week

I've placed these comboxes on a form and then used the following on the
click event

DoCmd.OpenReport "Rpt_320fuga", acPreview, , "[Year] = " & Me.Combo7 &

"
AND

[Week] = " & Me.Combo5 & " and "[Year] = " & Me.Combo8 & " AND [Week]

= "
&

Me.Combo9

This doesn't work. Can U help me

Thanks
Ticotion

:

Leaving aside your non-normalised database (see Douglas's post)

Why don't you just base your form on your Tbl320 then your combo box can
filter it to show the required row. Why go to all the trouble of

using
an

update or append query?

You say
So If a user choose year = 2008 and week = 21

So are you now saying that your combo have 2 columns, one for the week
number and one for the year

Or are you saying that you have 2 combos, one for the week number,

one
for

the year.
(I'd have thought this second design would be the better)

You could filter a continuous form with these combos with code like

this
in

the AfterUpdate event of the second combo

If IsNull(Me.Combo1) then
'check if the user has entered a year
MsgBox "Please choose a year"
Else
Me.Filter = "[Weeknb]=" & Me.Combo1 & " AND [Yearnb] = " & Me.Combo2
Me.FilterOn = True
End If

or if have you put your table into an unbound form as a subform and

you
want

to filter the subform:

If IsNull(Me.Combo1) then
'check if the user has entered a year
MsgBox "Please choose a year"
Else
Me.YourSubformName.Form.Filter = "[Weeknb]=" & Me.Combo1 & " AND [Yearnb] =
" & Me.Combo2
Me.YourSubformName.Form.FilterOn = True
End If



Evi



Hi Evi

It is proberbly very simple. I will try to explain it in another way.

My main tabel is called tbl_320 and looks like the following:

ID year week monday tuesday wedensday
1 2008 1
2 2008 2

I have a combo box call year and a combo box called week getting

data
from

tbl_320.

Then I have a box called monday, one called tuesday and so on

relating
to

each colounm in tbl_320.

The user should input produced amount in each box based on the selection
criteria year and week.

So If a user choose year = 2008 and week = 21 the monday, tuesday boxes
will
show the input values for those fields (if any) or the user can

input
new

values.

Hope this is a better explanation

Thanks again for your help

Ticotion

:



This still isn't making sense to me.

'The name of the field Produced amount is Monday (eh???). I also have a
field
tuesday, wedensday and so on, where the same thing should happen'.

Are you saying that you have Fields in your table called Monday, Tuesday
Wednesday etc?
(if yes, you may need to check your database design in the TableDesign
newsgroup)
or are you saying that you have text boxes in an unbound form

which
you

have
named (in Properties) Monday, Tuesday, Wednesday

Does Tbl320 also contain a field which contains numbers (1, 2, 3 etc)
for
each week? Or does it contain dates. Are you trying to add a new new
record
to Tbl320 or edit a record already there? ie are you trying to

run
an

append or an update query?

Is tbl320 set out like some kind of spreadsheet with the

weekdays
being

used
as field names and the week numbers typed down the side?

Is there a reason why you can't base a form on this table and

enter
the

data
directly into the table via the form which you can filter using

a
combo?

Or do you want your form to total the text boxes Monday, Tuesday
Wednesday
because this is an Unbound text box and enter the results into your
Tbl320
in a field called ProducedAmount next to the Week field with the chosen
number

I think there may be something you have missed out of your explanation.
Evi










Hi Evi

Name of combo boks is CBO2 which looks up week values in tbl_320.

The name of the field Produced amount is Monday. I also have a field
tuesday, wedensday and so on, where the same thing should happen.

When a user inputs numbers in the "Monday" field or Tuesday

field
and

so
on,
the user should by pressing a button save the input values for the
week
choosen, in tbl_320.

If nothing is listed then nothing should be written in the table.

I've tried to use your code but can't realyy get it to work.

Thanks for your help
Ticotion

:

It might help you to give details such as name of combo box, name of
table,
name of field for Produced Amount. it will make it easier

for
you to

read
the code too. It also saves us having to assign names to them.

Since this is a simple coding problem, I assume you know how

to
use

events.

So the code in the After Update Event for the combo will be
something
like
this

Me.ProducedAmount = (DLookup("[LookedUpValue]",
"DatasourceTable2","[WeekNumber]=" & Me.MyCombosName)

You'll need to put in the real names

You will need to decide what to do if there is nothing

listed
for

that
WeekNumber in your datasource table - look up the NZ

function
for

instance.
Evi

Hi

I have a simple form coding problem.

I have a combination box that contains week numbers. Furthermore I
have a
field for produced amount. When a user selects a

weeknumber I
want

the
form
to find the value in the datasource tabel, so that the

input
in

the
produced
amount field are saved here. Can you help me?

Thank you for your help and input
Ticotion