Populate with dates

[cymrudesign]

I have a form in datasheet style .....it only has 3 columns Id ,date and hrs what i would like to do is poulate the date column with the dates for the month based on the first entry I.E if the first dat inputted is 1/7/06 or chosen somehow )it would fill the remaining dates for that month into the column any ideas ???

 

[Matt Greatorex]

If, for example, the fields were called text1 and text2, you could use the after update event of the field into which the data will be entered.
e.g.
text2 = Dlookup("the value","tablename","tablename.datecolumn = "& text1).

You may need to convert the value of text1 into a date using, say, CDate, but that should give you the basics.

P.S. Purely out of curiosity, whereabouts in Wales are you based, or is the name a coincidence?

 

[cymrudesign]

im in north wales isle of anglesey any chance you could dumb that explanation down to my stupid level .it sounds like it should work but i havet got a clue where to start

 

[Matt Greatorex]

Nice part of the world.

Okay, forgive me if this sounds at all patronising, but I'll try to base my explanations on the assumption that you know as little as I did, when I started with this stuff. I'm going to assume the same field names, for now.

Open the form in design mode and select the text1 field.
On the Event tab, go to the After Update row.
Click in it and you'll see three dots appear to the right of it.
Click on these and pick Code Builder.
Type

text2 = Dlookup("X","Y","Y.Z = "& text1)

(where X = the field holding the value you want to find,
Y = the name of the table,
and Z = the field holding the data value you just entered into text1)

As far as the CDate part is concerned, your best bet is to look at the Access Help as it explains it pretty well. Simply put, it means you want to compare the value in the text1 box - which is a string - with a date from your table. If you were comparing two strings or two dates you wouldn't need to make the conversion. If you do need to use it, the code would look something like

text2 = Dlookup("X","Y","Cdate(#Y.Z = "& text1 & "#)")

Hope that's a bit clearer.

 

[cymrudesign]

not patronising at all....its my lack of knowledge ;-)

so if i get you right ( and thats a big if with me ) i wil have to create another txt box to control the column dates .

attached a screen shot od what im trying to do....the list just needs to be a months worth of dates to save the user filling them in .


P.S I see you were from the south and totally off topic we have a north and south wales contestants in UK big brother this year and a canadian as well ( sorry i do big brother sites as a sideline from corporate sites its compulsive )

 

[Matt Greatorex]

I may well have misled you here (much easier to understand now I've seen the picture). Would I be right in thinking that if you had six dates in January and five in February, you only want to see two rows - one for each month? If so, then you can do the whole thing with a query.

What's the table structure?

 

[cymrudesign]

table is just patient id (Auto) and date ( manually entered at the min ) and hrs from the pulldown..........all i need to see in that screen is 1 to 30/31 days in the month...................then clear it for the next months entries.......;-)

 

[Matt Greatorex]

Okay, hopefully the following will include what you need and you can get on with enjoying the weekend (five hours before I will ). You may need to make the odd tweak here and there, but these should get you started.

After you update the 'Patient ID' field or the (newly created, if necessary) 'Record Date' field on the main form, use the After Update event of the field to set the subform's record source to any of the following:

To just get all patient details:

Select [patient id],[date]
From tablename
Order by [date];

To just get the dates for a particular month:

Select [patient id],[date]
From tablename
Where tablename.[date] = some date you entered in a new field
Order by [date];

To just get the records for a given patient, irrespective of date:

Select [patient id],[date]
From tablename
Where [patient id] = the value from the patient id field, top left
Order by [date];

To get just the first date from each month for a given patient:

Select [patient id],
(Select Min([date] From tablename Where Format([date],"mmmm") = "January") as Min_Jan,
..
..
..
(Select Min([date] From tablename Where Format([date],"mmmm") = "December") as Min_Dec,
From tablename
Order by [date];

One last comment: calling the field in the table 'Date' may have been a mistake, as it's one of Access' reserved words and might cause you some confusion later on.

Hawddamor.

 

[cymrudesign]

i must say you know your stuff there ...im so not a logical person .... i think i messed up at the early stages somewhere .....at the moment the system works apart fromthe user having to change each date of the month every month .........ideally what i need is a form with a combo boxes to select the day, month, and year and have a column update to show all 30/31 days and dates for that month attached is a excel mockup of what i need it to do . i just need 30 possible dates for each client that can be changed from month to month to print off monthly . its more like a register sort of thing the only recall of the data will be on the end of month reports , but i think i have that bit sorted i think im going mad with it ahhhhhh lol

 

[cymrudesign]

ok i have gone round this in a different way..i dout very much that its correct but it might just work i have arranged my form with 31 drop down boxes that have the hours for the day, these are called 1st 2nd etc ( a bit drastic i know )...im just working on the report side of it and are now trying to add them all up im trying

=Sum[1st]+[2nd]+[3rd]+[4th]+[5th]+[6th]+[7th]+[8th]+[9th]+[10th]+[12th]+
[13th]+[15th]+[16th]+[17th]+[18th]+[19th]+[20th]+[21st]+[22nd]+[23rd]+
[24th]+[25th]+[26th]+[27th]+[28th]+[29th]+[30th]+[31st]

but must be doing something really wrong as im getting zero's anyone want to point me in the right direction ?

 

[RV]

What you need is a bit of VBA code.
I've included a quick and dirty sample that should help you getting strated.

You'll need to sort out a cleansing routine (remove all empty Visit rows after month has ended) yourself.

RV

 

[cymrudesign]

many thanks for that ... i have all of it sorted now apart from the adding of the 31 txt boxes and the purging at the end of the month. The bit i had to overcome was the manual inputting of all the dates ( all the clients are booked in in adviance for the month) this would mean 31 dates for 600 clients , so this is why i went down the 31 combo box routes

 

[Rich]

It sounds as though you're tying to use Access as a spreadsheet

 

[cymrudesign]

i would have used a spreadsheet if they didnt want to print off invoices from it .....any ideas how i can add up the multiple boxes total? my syntax must be way off ( see above)

 

[Rich]

You really need to do some reading on relational databases, normalisation etc.
Access is not a spreadsheet, if you aproach it with a spreadsheet frame of mind you'll end up with no end of problems, you don't need 31 textboxes to display data for a month.
You can make it look like a spreadsheet but the two are entirely different.

Have a look at some of the sample databases created by the Access Wizards to get started

 

[cymrudesign]

i have normalisation o only have 2 tables clients details and visits ...ir all works a treat adding client form etc . i had to change it because you would have to manualy input the dates for each day on each client . currently i only have 2 things left to fix.......i think ill stick to css +html ;-)

 

[Rich]

So it's the data entry form that you want to look like a spreadsheet?

 

[cymrudesign]

yes managed that the form now works as they need.... its just totalling the 31 combo boxes on a report is my main problem at the minute ( im sure there wil be more )