Combination box

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Ticotion - 22 May 2008 15:10 GMT

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

Reply to this Message

Evi - 22 May 2008 20:47 GMT

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
>
[quoted text clipped - 7 lines]
> Thank you for your help and input
> Ticotion

Reply to this Message

Ticotion - 23 May 2008 14:22 GMT

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
[quoted text clipped - 27 lines]
> > Thank you for your help and input
> > Ticotion

Reply to this Message

Evi - 23 May 2008 22:36 GMT

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
>
[quoted text clipped - 45 lines]
> > > Thank you for your help and input
> > > Ticotion

Reply to this Message

Ticotion - 24 May 2008 18:48 GMT

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.
>
[quoted text clipped - 84 lines]
> > > > Thank you for your help and input
> > > > Ticotion

Reply to this Message

Douglas J. Steele - 24 May 2008 19:08 GMT

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

Signature

Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no private e-mails, please)

> Hi Evi
>
[quoted text clipped - 124 lines]
>> > > > Thank you for your help and input
>> > > > Ticotion

Reply to this Message

Evi - 24 May 2008 20:20 GMT

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
>
[quoted text clipped - 113 lines]
> > > > > Thank you for your help and input
> > > > > Ticotion

Reply to this Message

Ticotion - 26 May 2008 15:50 GMT

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)
>
[quoted text clipped - 168 lines]
> > > > > > Thank you for your help and input
> > > > > > Ticotion

Reply to this Message

Douglas J. Steele - 26 May 2008 16:04 GMT

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.

Signature

Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)

> Hi
>
[quoted text clipped - 219 lines]
>> > > > > > Thank you for your help and input
>> > > > > > Ticotion

Reply to this Message

Jan Baird - 27 May 2008 20:52 GMT

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

Reply to this Message

Evi - 26 May 2008 17:16 GMT

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
>
[quoted text clipped - 192 lines]
> > > > > > > Thank you for your help and input
> > > > > > > Ticotion

Reply to this Message

Ticotion - 28 May 2008 12:58 GMT

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.
[quoted text clipped - 234 lines]
> > > > > > > > Thank you for your help and input
> > > > > > > > Ticotion

Reply to this Message

Evi - 30 May 2008 21:59 GMT

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
>
[quoted text clipped - 254 lines]
> > > > > > > > > Thank you for your help and input
> > > > > > > > > Ticotion

Reply to this Message

Jan Baird - 27 May 2008 20:50 GMT

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

Reply to this Message