display fields based on a count

Hi all

I have unbound fields on a form to capture dates entered by the user, the 
field [PlannedDays] holds the number of relevant days. The fields are named 
Date2, Date3.... to Date11. Is it possible to only display the correct 
amount of date fields based on the value in [PlannedDays]? eg if 
[PlannedDays]=3 then only date fields [Date2], [Date3] and Date4] should be 
visible for input on the form? I will then need to add these dates via code 
to a table called [ItineraryDates] field [ReviewDates] (one record per 
date).

Thanks... Sue 


0
Sue
4/20/2010 9:56:09 AM
access 16762 articles. 3 followers. Follow

6 Replies
952 Views

Similar Articles

[PageSpeed] 53

hi Sue,

On 20.04.2010 11:56, Sue wrote:
> I have unbound fields on a form to capture dates entered by the user, the
> field [PlannedDays] holds the number of relevant days. The fields are named
> Date2, Date3.... to Date11. Is it possible to only display the correct
> amount of date fields based on the value in [PlannedDays]?
Yes, but this table structure sounds terribly wrong. Can you explain 
what you're trying to achieve and what data you're using here?

You should normalize this to something like this:

Plan:
ID, PlannedDays
1, 1
2, 2

PlanDetail:
ID, idPlan, PlanDate, PlanOrder
1, 1, #1/1/2000#, 1
2, 2, #1/1/2000#, 2
3, 2, #1/1/1999#, 1



mfG
--> stefan <--
0
Stefan
4/20/2010 11:19:23 AM
The table structure is normalised don't worry! This takes an activity from 
table1 (which is a list of possible activities to carry out) and stores it 
in Table2 + Table3. Table2 is the activity details and Table3 is the dates 
(ItineraryDates). Currently the user enter a Startdate for an activity and 
based on the number of days, the system calculates in code which actual 
dates the activity falls in and enters them in ItineraryDates as individual 
records, including a Sat or Sunday only if those tickboxes have been 
selected.
eg for a 2 day activity for ItineraryID 1 it stores in ItineraryDates: 
[ItineraryID] 1, [ReviewDates] 01/05/10
[ItineraryID] 1, [ReviewDates] 02/05/10

The reason for this form is occassionally the dates are not always 
consecutive, the activity might be split ie they may do it on a Tuesday and 
continue it on Thursday etc. There is no logic I can easily apply for the 
system to then work out what the dates should be. So I have added a [Split] 
tickbox. If Split = True then it displays a form for the user to enter the 
'actual' dates manually that are to be inserted into the ItineraryDates. The 
dates are entered manually before the Itinerary is created so at this stage 
there is no ItineraryID. I guess I could try this as a continuous form and 
have each date entered as a new record but I though it would be easier to 
have unbound text boxes and to capture each one (if not null) as a new 
record in ItineraryDates.

I have got part way there in displaying the correct number of fields:

Private Sub Form_Open(Cancel As Integer)
Dim noDays As Integer, NameVariable As String

noDays = Forms![frm Resource Planning]![PlannedDays]

For i = 2 To noDays
    NameVariable = "Date" & i & ""
    Me.Controls(NameVariable).Visible = True
Next i

End Sub

This display the correct textboxes for the user to enter the dates, but it 
doesn't display their corresponding labels?



"Stefan Hoffmann" <ste5an@ste5an.de> wrote in message 
news:u1xcVtH4KHA.5588@TK2MSFTNGP06.phx.gbl...
> hi Sue,
>
> On 20.04.2010 11:56, Sue wrote:
>> I have unbound fields on a form to capture dates entered by the user, the
>> field [PlannedDays] holds the number of relevant days. The fields are 
>> named
>> Date2, Date3.... to Date11. Is it possible to only display the correct
>> amount of date fields based on the value in [PlannedDays]?
> Yes, but this table structure sounds terribly wrong. Can you explain what 
> you're trying to achieve and what data you're using here?
>
> You should normalize this to something like this:
>
> Plan:
> ID, PlannedDays
> 1, 1
> 2, 2
>
> PlanDetail:
> ID, idPlan, PlanDate, PlanOrder
> 1, 1, #1/1/2000#, 1
> 2, 2, #1/1/2000#, 2
> 3, 2, #1/1/1999#, 1
>
>
>
> mfG
> --> stefan <-- 


0
Sue
4/20/2010 11:42:51 AM
hi Sue,

On 20.04.2010 13:42, Sue wrote:
> The table structure is normalised don't worry!
Okay, as long as you know what you're doing ;)

> I have got part way there in displaying the correct number of fields:
>
> Private Sub Form_Open(Cancel As Integer)
> Dim noDays As Integer, NameVariable As String
>
> noDays = Forms![frm Resource Planning]![PlannedDays]
>
> For i = 2 To noDays
>      NameVariable = "Date"&  i&  ""
>      Me.Controls(NameVariable).Visible = True
> Next i
>
> End Sub
>
> This display the correct textboxes for the user to enter the dates, but it
> doesn't display their corresponding labels?
Ah, then the labels are not associated to the text boxes.

btw, you should use the form's On Load event for code like yours. In the 
On Open event you should only place code which determines whether a from 
is displayed or not (the Cancel parameter).


mfG
--> stefan <--
0
Stefan
4/20/2010 11:57:35 AM
The labels are associated, at least there is no little green arrow in the 
top left corner and exclamation mark stating 'unassociated' etc....

Thanks for the tip on which code event to use, I don't know what the 'rules' 
are - I just assume if it works its ok.

"Stefan Hoffmann" <ste5an@ste5an.de> wrote in message 
news:eXBQsCI4KHA.4804@TK2MSFTNGP04.phx.gbl...
> hi Sue,
>
> On 20.04.2010 13:42, Sue wrote:
>> The table structure is normalised don't worry!
> Okay, as long as you know what you're doing ;)
>
>> I have got part way there in displaying the correct number of fields:
>>
>> Private Sub Form_Open(Cancel As Integer)
>> Dim noDays As Integer, NameVariable As String
>>
>> noDays = Forms![frm Resource Planning]![PlannedDays]
>>
>> For i = 2 To noDays
>>      NameVariable = "Date"&  i&  ""
>>      Me.Controls(NameVariable).Visible = True
>> Next i
>>
>> End Sub
>>
>> This display the correct textboxes for the user to enter the dates, but 
>> it
>> doesn't display their corresponding labels?
> Ah, then the labels are not associated to the text boxes.
>
> btw, you should use the form's On Load event for code like yours. In the 
> On Open event you should only place code which determines whether a from 
> is displayed or not (the Cancel parameter).
>
>
> mfG
> --> stefan <-- 


0
Sue
4/20/2010 1:55:43 PM
For ref - the labels settings were not visible as default, I had to turn on 
visible for them all to make it work. It now displays only the labels that 
are associated to the controls I make visible...

"Sue" <sportybabesue@hotmail.co.uk> wrote in message 
news:eui4tEJ4KHA.3352@TK2MSFTNGP02.phx.gbl...
> The labels are associated, at least there is no little green arrow in the 
> top left corner and exclamation mark stating 'unassociated' etc....
>
> Thanks for the tip on which code event to use, I don't know what the 
> 'rules' are - I just assume if it works its ok.
>
> "Stefan Hoffmann" <ste5an@ste5an.de> wrote in message 
> news:eXBQsCI4KHA.4804@TK2MSFTNGP04.phx.gbl...
>> hi Sue,
>>
>> On 20.04.2010 13:42, Sue wrote:
>>> The table structure is normalised don't worry!
>> Okay, as long as you know what you're doing ;)
>>
>>> I have got part way there in displaying the correct number of fields:
>>>
>>> Private Sub Form_Open(Cancel As Integer)
>>> Dim noDays As Integer, NameVariable As String
>>>
>>> noDays = Forms![frm Resource Planning]![PlannedDays]
>>>
>>> For i = 2 To noDays
>>>      NameVariable = "Date"&  i&  ""
>>>      Me.Controls(NameVariable).Visible = True
>>> Next i
>>>
>>> End Sub
>>>
>>> This display the correct textboxes for the user to enter the dates, but 
>>> it
>>> doesn't display their corresponding labels?
>> Ah, then the labels are not associated to the text boxes.
>>
>> btw, you should use the form's On Load event for code like yours. In the 
>> On Open event you should only place code which determines whether a from 
>> is displayed or not (the Cancel parameter).
>>
>>
>> mfG
>> --> stefan <-- 
>
> 


0
Sue
4/20/2010 2:03:55 PM
hi Sue,

On 20.04.2010 15:55, Sue wrote:
> The labels are associated, at least there is no little green arrow in the
> top left corner and exclamation mark stating 'unassociated' etc....
Weird. In this case it should work...


mfG
--> stefan <--
0
Stefan
4/20/2010 2:11:10 PM
Reply:

Similar Artilces:

Return a cell refernce based on a value
I have a value in worksheet A which is "x". In worksheet B column A have a list of values i.e. a,b,c,d...x,y,z and in column B I hav values relating to those in coumn A i.e. column A row 1 value is "a column B row 1 value is "apple", column A row 2 value is "b" and colum B row 2 value is "balloon", how do I get teh first worksheet to rea the value in one cell, find it in column A of the other worksheet an give back the value on teh same row in column B? Thank -- Message posted from http://www.ExcelForum.com Hi use the VLOOKUP function =VLOOKU...

Dual Displays
I want to connect my Sharp Aquos flat screen TV to my computer, and see my desktop on both the TV and computer monitor. Windows Vista Home Premium. HP Pavilion a1610n computer. Galaxy GeForce 8400 GS graphics card, outputs: 1-VGA, 1-DVI, and 1-SVGA. There is no HDMI port on the card. I connected a VGA cable between the motherboard "on-board" VGA port and the TV, figuring that I could use that output to work with the TV. The computer monitor is plugged into the GeForce graphics card. Tried the Desktop "Display Setting". It says, the number 2 monitor is "N...

Display a message box "this entry is a duplicate"
How can I display a message box on a form if the shipment number is a duplicate? I need a message box to pop up when user attempts to tab out of the field. Thank you. Something like: Private Sub txtShipNum_BeforeUpdate(Cancel As Integer) If DCount("*", "MyTable", "ShipNum = '" & Me.txtShipNum & "'") > 0 Then Msgbox Me.txtShipNum & " already exists." Cancel = True End If End Sub This assumes that your text box is named txtShipNum, that the name of the field in table MyTable is ShipNum...

Folder could not be displayed
I am running Windows XP and Outlook Express 6 just fine then one day my husband does something to my computer and his email account won't work. My email is working just fine but in his he cannot open his inbox. It says folder could not be displayed, and when you send and receive it gives you this error message: "An unknown error has occured. Account: 'incoming.verizon.net'. Server:'incoming.verizon.net'.Protocol:POP3, port: 110, Secure(SSL): No, error number: 0x800C013B" If anyone could help me with this I would greatly appreciate it. Thank you T...

Dynamic Range Based on Cell Result not Empty Cell
I use dynamic ranges in excel. I use them to name ranges that vary i size due to varyin data. For example, I may dump data in F2 which ma span 3 rows or 100 rows. I use the following formula to name th range: =OFFSET($F$2,0,0,COUNTA($F2:$F65536),1) However, I now want to define the range based on the cell result. Tha is assume that I have a constant formula from F2 to F102. However th cells in the column either have a number in them or a "" in them. Th formula above will automatically size the range to F2:F102 becuas there is a formula in every row. How can i write a formua...

Using "Count" in Queries
I'm having trouble with a query I'm working on. I am running it with multiple criteria and it works perfectly fine. However, when I change the "Total" row from "Group by" to "Count" the query comes up blank. The only time the query performs as it should, with the counts, is when I delete the criteria for all the other fields. How can I get the count to work while still keeping the specific criteria the same? Thank you in advance for the help! Tim wrote: >I'm having trouble with a query I'm working on. I am running it with mul...

how to display values in 3 cells into one cell
Anyone can help me how do I display the 3 separate different values in 3 cells into just one single cell ? thanks so much in advance. Either there was a typo or there's an echo in here. That line should have been: = A1 & " " & B1 & " " & C1 "Bradley Dawson" <bradleydawson@earthlink.net> wrote in message news:l464b.2224$Lk5.2093@newsread3.news.pas.earthlink.net... > In the target cell, type: > > = A1 & B1 & C1 > > where A1, B1, C1 are the cell addresses that you want to concatenate. > > If these have number...

field based on field
I feel like I've asked this before. How do I make a field based on another field in the same query? exp1=a+b exp2=val(expr1) How do I write expr2? Kou On Thu, 13 Dec 2007 09:01:02 -0800, Kou Vang wrote: > I feel like I've asked this before. How do I make a field based on another > field in the same query? > > exp1=a+b > > exp2=val(expr1) > > How do I write expr2? > > Kou Your above question can have more than one result depending upon field datatype and what you mean by using the "+". Did you want to concatenate or add? Since your...

bound unbound fields into newrecord vb2005
hi, I'am trying to make an vb2005 application.I've a connection with a accessdatabase. on a winform i've some field which were scanned and are displayed on a winform. I compare this data with the data in the database on the same form, in a dtagridview. However , when i've receive totaly new data, i've nothing to compare , meaning i have a new client. how can i add these new data which are displayed in unbound textfields into a new record in the database ? regards, Jef ...

find and count days in blocks of consecutive dates
I need to find a way using Excel to find blocks of consecutive dates in a list and then count how many days are in each block. Any help is appreciated. Hi if i'm understanding you correctly, you can use the COUNTIF function =COUNTIF(range,">="&datevalue("lowest_date"))-COUNTIF(range,">"&datevalue("highest_date")) e.g. =COUNTIF(A1:A23,">="&DATEVALUE("2/10/04"))-COUNTIF(A1:A23,">"&DATEVALUE("4/10/04")) counts all the days between 2/10/04 and the 4/10/04 inclusive PS you might nee...

How to select & display distinct values
Hello, I need help with a form please. My form has two controls, the first being a combo box. This box should select the company name from a lookup table. The lookup table has only two companies in it, however, they each are repeated several time. Company 1 has about 10 rows, company 2 has 3 rows. I used the query builder and came up with the statement: "SELECT DISTINCT Lktbl_Role_Master.CompanyName FROM Lktbl_Role_Master;" When I run the statement directly from query view, it executes perfectly and returns exactly two rows (one for each company, in a datasheet view) ho...

Display Time in Outlook
The time setting in my Outlook is one hour behind my actual time. I cannot determine how to change the time. Outlook is the only item that is incorrect. Other display times in operating system are correct. Thanks, Jodi ...

PK and FK Displayed
I am a complete Visio newbie, and cannot find the answer I need in books or online help. I am trying to create ER diagrams from scratch, and not connected to real tables, using VEA. I have added tables ('Entities') to the form, connected them with a Relationship connector, and, in the Database Properties window I've assigned the end points to a Primary Key on the left and a Foreign Key on the right. But the model shows the connector as running between the top colums on both sides, whereas I want the keys to be in that upper box and provide the connector's end points. Wh...

Formulas are displayed, results are not
Hello, For some reason, when I enter a formula into a cell and press enter, the formula just stays there. Anyone know what I can do to show the results? I tried pressing Cntrl + `, but that didn't work. FYI, when I cut and paste a column from a separate workbook, that column works like normal (i.e., the formulas produce results). Thanks for any suggestions! Hi Mike, Maybe your cell is preformatted as Text. Try formatting to general then erase your = and type it in again. HTH Martin "Mike C" <js2k111@yahoo.com> wrote in message news:f699bcd4-ad82-4a88-9759-5638c147b...

Stop displaying a line
I have a line graph/chart and it gets its values using a formula and the values of another column. However this poses a problem because if there is nothing in the targetted column the formula returns 0 cause the line to drop to the bottom, instead of casuing the line to just end until a value is added to the target column. How can I do this? Change your formula to use NA() if the cell is empty. For example: =IF(A1="",NA(),A1) To hide the resulting #N/A errors on the worksheet, you can use conditional formatting. There are instructions here: http://www.contextures.com/xlC...

unattached textboxes disappear when a msgbox is displayed on a tab control
I'm using Access 2003. I have a form with a tab control on it. I was having problems with labels flashing and I followed the suggesting from Allen Browne - http://allenbrowne.com/ser-46.html. The flashing went away but now when a msgbox is displayed the unattached textboxes that I'm using as labels disappear. They come back as soon as the msgbox is removed from the screen. I've searched google looking for an answer but have come up short. Does anyone know why I'm experiencing this behavior and how to make it go away? Thanks, Rick That *is* strange. Have you tr...

A question about Console window(display or not display)
I've redirected the (stdin stdout stderr) of a console window process I spawned to a pipe. And I want to display the redirected console window, the question is: If I display it(set Create_new_CONSOLE in createprocess and some flags in startupinfo), I cannot use the redirected way of pipe, which means if i do not display, i can use the pipe communicating with the child process. Does it mean that as long as I disply the console window, windows will change stdin stdout to "screen&keyboard" instead of my pipe? That's right. If you redirect stdin or stdout then you preemp...

Group Discussion Fielded
Hello, Apologies in advance if I am not using the most appropriate terminalogy. I am trying to find a way to structure a group discussion on several topics all within the same emails which people update. For instance, suppose there are 3 individuals (not on the same exchange network) discussing 2 topics. If this were excel, I would have 3 rows and 5 columns e.g. Blank John Jane Joe End Conclusion Topic 1 blah 1 blah 2 blah 3 blah 7 blah 8 ABC Topic 2 blah 4 blah 5 blah 6 blah 9 ...

Display or not display series
I have a graph with four series but some of the series may be blank, i.e. the series name is <blank series>. Is there a way to not display these series on the graph and/or legend if they are blank, and display them if they are without deleting the series? Thanks! Clay Create a chart from all the data Select the data on which the chart is based. Choose Data>Filter>AutoFilter In the heading cell for the series name column, select (NonBlanks) from the dropdown list The series with blank name cells will be hidden in the chart. Clay wrote: > I have a graph with four serie...

does Excel support multiple display monitors?
Hello All Excel Experts How do I move chart windows to multiple display montors?? (one computer runnng three monitors... Excel Charts will not move off the primary monitor. Your advice is EXTREMELY appreciated thanks. Denni ...

Creating fields like gateways, when met, unlock next step
I am researching how to create "gates" in a database so that when someone initilializes the process filling out a form, they cannot progress beyond a certain point in the process until a specified field is "electronically" signed by the "keyholder" person authorized to approve progression to the next step. The "next step" could be gaining entry into another form or subform. I've not been able to come across anything helpful yet. Any help is much appreciated. Thank you in advance, "Aso" <Aso@discussions.microsoft.com>...

table based i one field splitted in 2 fields
Hi everyone, I have a txt file with product EAN and label quantity to print. 3564700010822 2 3564700010983 5 3564700422687 10 I have a form to print label quantity. What i pretend is "convert" the table in a 2 fields table or query like: ProductCode |Quantity 3564700010822|2 3564700010983|5 3564700422687|10 Any help is apreciated. -- Thanks, Sorry if my english isn''t correct, but I''m from Potugal ;) Emanuel Violante Galeano On Thu, 29 Apr 2010 07:27:01 -0700, Emanuel Violante <EmanuelViolante@discussions.microsoft.com>...

Displaying an equation
I have an equation setup that is referencing a few other cells that have equations in them. I want to have a cell that displays this equation with the answers that are calculated from these other cells displayed as values in the new equation. Any help would be greatly appreciated. Thanks Check out http://www.mvps.org/dmcritchie/excel/formula.htm#getformula In article <E4143E2B-360A-4B43-955F-73BF8EC60694@microsoft.com>, "hoising8" <hoising8@discussions.microsoft.com> wrote: > I have an equation setup that is referencing a few other cells that have > eq...

Serialisation
A general query Im serialising a person object with the following member variables Class Person Private _forenames As String Private _surname As String Private _dob As Date Private _marital_status As MaritalStatus 'Public Get and Set Properties here for each of the above ' I'll not detail them all to save space but Ill show attributes <XmlElementAttribute(Elementname:="forename")> _ <XmlElementAttribute(Elementname:="surname")> _ <XmlElementAttribute(Elementname:="dob"...

New Email does not display
When new email arrives you don't know it. You don't know you have new mail until you click on an existing email in your inbox. Any ideas? It's a setting. But... 1. You don't tell us the program you're using. 2. We can't guess if it's Outlook or Outlook Express. 3. This is the Excel newsgroup; you're in the wrong place. :) ******************* ~Anne Troy www.OfficeArticles.com "Travis" <Travis@discussions.microsoft.com> wrote in message news:D864BC01-7D98-48DA-A132-614D02E45963@microsoft.com... > When new email arrives you don't know ...