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
1122 Views

Similar Articles

[PageSpeed] 13

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:

Conditions (e.g. in Onload Jscript) based on user ID?
Is it possible to make conditions (e.g. Jscript in Form Onload) based on which user is logged on? Simple example: If (user = boss); {"Hide tab with IFrame containing link to favourite football website"} I haven't been able to find any code that does this. Thanks For this sort of behaviour you would need to call the Crm webservice using javascript. I have included a script below to get the current user id. var soapBody = "<soap:Body><Execute xmlns='http://schemas.microsoft.com/crm/2007/WebServices'><Request xsi:type='WhoAmIRequest' /&g...

Deed a small data base
Hi hope you can help. I need a small data base. (49 ford truck )will bring up the 6 pieces of glass pattern numbers I need to cut the glass from. -- Thanks Mike Send an example of your data to me and I will have a look at it johncalder54@hotmail.com "hotrodflatglass" wrote: > Hi hope you can help. I need a small data base. > (49 ford truck )will bring up the 6 pieces of glass pattern numbers I need > to cut the glass from. > -- > Thanks > Mike ...

Count if formula with two variables
Hello, Am trying to figure out a formula in a cell to analyse data in tw columns. One column will have a referance and the frequency of th corresspomnding data in the other column need to be counted -- shrikan ----------------------------------------------------------------------- shrikant's Profile: http://www.officehelp.in/member.php?userid=474 View this thread: http://www.officehelp.in/showthread.php?t=123405 Posted from - http://www.officehelp.i attach an example. "shrikant" wrote: > > Hello, > > Am trying to figure out a formula in a cell to analyse dat...

Opening Access without Menus displayed
Access 97 I have created a simple data lookup form for a customer to look up his own jobs in a shared database - there is only the one form in the mdb(e) that auto-opens on application startup. I have unchecked all the Startup functions etc. The program auto-opens the form okay but there is the default menu bar displayed across the top of the application window. The tool bars do not show. Is there a way to open the program and not show ANY menus as well? My form close event also quits the application. I intend to provide this as an mde. Any ideas gratefully appreciated Piri Piri wrote: &...

Pivot table count error
Hi, So I have a unique dataset based on some ID. I created a pivot table based on this dataset and later went in to add a couple of more records.When I refresh, I can see the new column created in the pivot table for the newly added records but there is no change in the sum total which is very worrisome becuase there should be since I have only unique records ..... e.g. 1. Original Pivot table Unit Total count of IDs A 31 B 77 C 184 D 7 E 29 F 139 G 11 H 40 I 134 Gran...

Export to Text Files Based on Field Value
I have an Access table with personal information (e.g., first name, last name, birth date, etc.) for thousands of people. The table is sorted by state. I want to be able to run a macro on that table that will create separate pipe delimited text files for each state with all fields included. Any help would be appreciated? -- Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/Forums.aspx/access-queries/201005/1 Instead of exporting the table, export a query based on the table. You will have to have some code that loops through the states and modifies the quer...

Graph axis to change based on user input
I'm using Excel 2010. I have a graph with dates along the x-axis and other data along the y. I would like the x-axis to change based on the start and ends dates entered by the user. There is a column with all the available dates and the associated data. Can this be done? any help on this would be great. Download FunChrt7.zip from Stephen Bullen's site. http://www.oaltd.co.uk/Excel/Default.htm Gord Dibben MS Excel MVP On Mon, 14 Mar 2011 03:34:07 -0700 (PDT), LROCCO <l_a_rocco@yahoo.co.uk> wrote: >I'm using Excel 2010. I have a graph with dates along the x-a...

Counting Months
Guys, I need to do something clever with months - although right now I'm not quite sure what the best approach is. I'm developing a yearly Company Ledger workbook with 13 sheets, one sheet per month, plus one 'Setup' sheet as the first sheet. The idea is that the 'Setup' sheet is filled in with all the company details, and this information is used to populate the appropriate cells in the subsequent twelve monthly ledger sheets. I'd like the monthly ledger sheets to calculate the (UK)VAT due to HM Revenue & Customs (I don't have a problem with the cal...

Count Based on Comparison with Two Fields
I recently learned that I can count the number of items in one column that match items in another column by using: =COUNT(MATCH( 'Sheet1'!G2:G15001, 'Sheet2'!A2:A15, 0)) However, I now need to count records on one sheet in which TWO fields match items in both of two separate columns (i.e., a comparison of two columns to two columns and a count of records in the first that have fields matching elements in the second) . My attempt to use the above statement with an AND failed. Can someone explain how to do this? Hi I would suggest that you post a before a...

Counting strings of consecutive numbers in a column
Hi I want to be able to count strings of consecutive numbers in the one column. eg: Seagrass Seagrass Seagrass Seagrass NA NA NA Seagrass Seagrass Seagrass NA ... So i want to count the number of times Seagrass occurs in the one string, then count how many times it occurs int he next string. eg in the example above: 4 & 3. I am at a total loss thanks Check out your previous post.. -- Jacob "SamG" wrote: > Hi I want to be able to count strings of consecutive numbers in the one column. > > eg: > > Seagrass > S...

merging 3 spreadsheets into one based upon a single common data field
OK I hope you guys can help me out. Here is the scenario. I have 3 spreadsheets. The main spreadsheet I working with has 500 lines with a unique number string. The next two spreadsheets have about 12000 lines but also contain the same unique string as the first spreadsheet. I want to search the 2nd and 3rd spreadsheet using the first spreadsheets unique id number and then take the information from the 2nd and 3rd spreadsheet and then merge it into the first. Does that make since? Any help would be great. -- xchosen --------------------------------------------------------------------...

Display System Time running real time
Hi how to display the system date time running real time in Dialog, including seconds ? I know declaring like: SYSTEMTIME systime; ZeroMemory(&systime,sizeof(SYSTEMTIME)); GetSystemTime(&systime); // gets current time then access the SYSTEMTIME structure: typedef struct _SYSTEMTIME { WORD wYear; WORD wMonth; WORD wDayOfWeek; WORD wDay; WORD wHour; WORD wMinute; WORD wSecond; WORD wMilliseconds; } SYSTEMTIME; but how to get it running in realtime as it elapses seconds by seconds like 8:56:01 AM Monday, December 02, 2008 ......

Displaying a product image in a portal web part
Is there any way to show a product image on any Portal page -- like how Order Management does when you click on an item to view more information. It shows the image that is specified in the Image field on the Internet Information screen in GP. Can this be done in a standalone web part? I'm running Business Portal 3, but is this possible in either version 3 or 4? Thanks. -Joe ...

Auto Populate a form field
I have two tables with 1:1 relationship. Both have same key field (SS Number). There are two forms, one for each table. When I open Form A there is a button to open Form B. When Form B opens, I want SS Number field to equal the SS Number on the current record in Form A. How do I do this? Thank you in advance. Well, IMO the easiest way would be to make Form B a subform of A, and linking via the common key. Or you could make Form A a tabbed form, and A would be the first tab and B would be the second. Just a couple of random thoughts... "A Bit Frustrated&quo...

Changing text based on criteria in an access report
Hello, I've got a textbox in a report, some records of which may contain text within { }. I need to find some way to format only that text (which may be in the middle of a string) so that it appears underlined and so that the brackets disappear. To illustrate, I posted a 5KB graphic here: http://img219.imageshack.us/img219/3523/jexamplemb2.png Does anybody have any suggestions or ideas? Thanks in advance! Mitch Access 2007 can format the text for you, using standard HTML for the underlining. Use the Replace() function to swap the braces for the HTML tags. Earlier versions of Ac...

How can I create a multiple field search box that use logical operators in an option box?
Hi, I need assistance in creating a multiple field search box, i have a screen shot but i couldn't find a way to include it in this message. I have been battling with this problem for weeks. I'm trying to use ms access 2003 to create a search box similar to one i saw in foxpro. It searches all the fields in only one table so each table has a search box for it. Here is a description for the search box for the community table which is one of the tables in the database. It has a drop down list that selects all the columns in the table such as area code for the community, community code,...

Summing and Counting based on 3 criteria
I am trying to sum and count based on 3 criteria from another tab with no luck. Example I want the sum of the criteria in column P in sheet2 if column B="Phoenix", column E="1", and column j="807" and return that to a cell in sheet1. Same scenerio with the counting. Any help would be very appreciated. Thanks Todd Hi try =SUMPRODUCT(--('sheet2'$B$1:$B$100="Phoenix"),--('sheet2'$E$1:$E$100=1) ,--('sheet2'$J$1:$j$100=807),'sheet2'$P$1:$P$100) if your numeric values are stored as real numeric values. -- Regards Fra...

Create Charts based on data in Table
I would like to create a series of charts that are dependant on dat stored in a table. Specifically I would like to pull data from th table based on Dates in a table for example. The Table contains Date in Column A, then a variety of other informaion going across th columns. I would like to extract and chart data based on a date rang say Jan 1st - Jan 31st. I would like to do this a formula based i named ranges like "PerOneStartDate" to "PerOneEndDate". This woul give flexibility in changing the reange without having to go to th table and select data. Thank yo -- MAtte...

Automatically send email based on date value
Is there way to send an email automatically based on a date field in a table? Some more information about what I am trying to do. I have a form where users enter in upcoming bids. I would like to send a reminder email out some specified time (ie, 14 days, 7 days, and again 3 days) prior to the bid being due based on the Bid due date field in the table. I don't want to have to go into the form and hit a button, I would just want it to review the table perhaps every time the database is open or even when the form is opened and send the email. The email only needs to go to one person, so ...

formula to retrieve the count of a certain value over a range of cells
Need to do some simple stats, and thought it should be easier. Looking for a function to give me a count of all the cells that equal certain value in a certain range. ie.. Give me a count of the number of cells that equal the number 1 i the range a1:a25. Help -- trev ----------------------------------------------------------------------- trevc's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=1683 View this thread: http://www.excelforum.com/showthread.php?threadid=32022 =countif(a1:a25,1 -- Gromi -----------------------------------------------------------...

Count if equal to first non error cell
Hi I tried to post this once so apologies if this is second post... I want to count the number of times 3 or -3 occurs in range A8:A15 depending on whether 3 or -3 comes first. If 3 is A8 then count how many times 3 occurs in the range. If A8 and A9 equal #n/a and A10 equals -3 then count how many times -3 is in the range (there many be 2 -3's and one occurance of a postive 3 for example) Thanks ...

Pivot Table Calculated field item
Hi, I want to create a calculated field that uses an if to base its calculation results. Therefore i have a field called Site which is a text and another field called materials which is the number of materials. =if(Site="Mexico", materials*300, materials*2000) I have tried this and it doesnt compute based on the Site field or at least the text isnt matching as I have changed the Site to Mexico and it still calculates based on the 2000 amount. Anyone know why? Thanks, Tim Since Site is a text field, the calculated field will evaluate all Sites as zero. So, materials for all...

basing TWO combo boxes on another combo box in a form.
I have been able to base ONE combo box on another combo box using an article at the Office online website. But I have THREE combos I need to 'join' (if thats right word). Customer combo box Delivery Address combo box Customer Contact combo box I have managed to limit the delivery address combo list to only the customer selected, but cannot seem to use the same code in the third box. Any help with this would be much appreciated. You should post the code you are using, along with a bit more information about the table structure. Are Delivery Address and Contact in the Customer ...

Can I calculate a field with 2 different filter criteria in one q
Hi, I have a data source table with a customer name field and various metrics fields, I'd like to sum metric "a" based on customer name being like "abc" and another sum of metric "a" where the customer name is NOT LIKE "abc" in one query, but I can't seem to figure this out, but, I'm a novice at this. Best I can figure is 2 different queries, then a 3rd query to bring the two together. Thanks -- Jim jimd wrote: >Hi, I have a data source table with a customer name field and various metrics >fields, I'd like to sum metric &...

QUERY FIELD SUM
In have the following in my first field. K:IIF([PL4HX4.RK]=1,"2",IIF([PL4HX4.RK]=2,"1","")) I have the following in my second field. H:IIF([PL4HX4.RH]=1,"2",IIF([PL4HX4.RH]=2,"1","")) What would i write in my third field to get the sum of K + H ? Thanks ed should be as easy as: L: [K] + [H] However, you see that you are returning "1" which is a string, as opposed 1 the number. You might want to change the values to 2, 1, and 0 depending on what you are trying to accomplish. >> What would i writ...