Populate 25 records only

[Scoffagus]

I have a form with 25 appointment times (records) for 7 days Fields). I need to enter the date for each of the 7 days against each of the time slots. I can achieve this, but with very lengthy code. This works Ok. I have 7 unbound controls named txtDay1 to txtDay7. The 7 day fields are called Day1 to Day7 . I enter in the different dates of the appointments in the 7 txtDay* controls. I then run the following code.
DoCmd.GoToRecord , , acFirst
If IsNull(Me![txtDay1]) = False Then
Day1 = [txtDay1]
DoCmd.GoToRecord , , acNext
Day1 = [txtDay1]
DoCmd.GoToRecord , , acNext
Day1 = [txtDay1]
This code is repeated 25 times to populate the 25 time slots with the date entered into the control txtDay1 so that against each time slot for Day1 is the date held in the control txtDay1.
This event is carried out for each of the seven days, so making a lot of lengthy coding. I am sure that using a record count or something, or a do, while, loop or something, that this bulky coding can be reduced to just a few lines of code, yet still do the same thing. I have tried all sorts but with no luck.
I would appreciate any help on this, as i am rather stuck on an important project.
Thanks for taking the time to at least read this.

Scoffagus

 

[raskew]

Not sure I understand your table design. Could you post the table design and some sample data.

 

[GeorgeWNYC]

Hi Scoffagus:

?? It seems that your 25 records will be identical, each with a sequence of 7 values (txtday1...txtday7) in fields day1...day7, respectively. ?? why?

Anyhow, the values to be assigned are on 7 controls on the form. Assuming that you create each of these controls sequentially, then they can be accessed as such.
=============
Go to immediate pane and paste
for x= 0 to [forms]![YourFormName].controls.count-1:debug.print x, [Forms]![YourFormName].controls(x).name:next

That will give you a list of each control and their ordinal number. Note the one for txtday1 [lets call it FirstBoxNumber]. Also make sure the ones for txtday2 through txtday7 are the next ones in the list.

Now, the fields in your table will have a similar numerical arrangement: try our loop again using
[Tabledefs]![MyTable].fields(x).name
And you get FirstFieldNumber

so your loop becomes
dim ff as form: set ff=forms![YourFormName]
dim rs as recordset: set rs = _
currentdb.openrecordset("Select * from MyTable&quot
for times = 1 to 25
rs.addnew
for offset = 0 to 6
rs.fields(FirstFieldNumber + offset) = _
ff.FirstBoxNumber + offset
next
rs.update
next


I'm just typing this out on the fly so the code
may not be perfect, but it's just to give you the idea.

Note that this code is in a module, and runs behind the scenes, however the form must be open to read the values.

hth,

George

 

[Scoffagus]

Thanks for the VERY quick replies from both raskew and Georgewyc. Perhaps i did not explain it all quite so well. My table/form has the following fields:
Time - This field has 25 records entered into it starting at 09.00 and steps in half hour intervals up to 21.00. These entries are permanent, providing 25 appointment slots for each day.
Date - These are variable dates. They refer to the dates of the days on which a specific event occurs, maybe two or three times a year. The times are permanent, but the dates are variable. There are seven of these fields named from Day1 to Day7. These seven fields require the date for the day of the appointments, so hence the requirement to enter into the 25 records for each Day1 to Day7 field, the value of the seven controls on the form into which the date is entered as a lookup value.(called txtDay1 to txtDay7. These are unbound controls on the form)
It looks like this on the form

Time Day1 Day2 Day3 Day4 Day5 Day6 Day7
09.00 1-1-01 2-1-01 3-1-01 4-1-01 5-1-01 6-1-01 7-1-01
09.30 1-1-01 2-1-01 3-1-01 4-1-01 5-1-01 6-1-01 7-1-01
10.00 1-1-01 2-1 01 3-1-01 4-1-01 5-1-01 6-1-01 7-1-01
And so on until the 25th record contains
21.00 1-1-01 2-1-01 3-1-01 4-1 01 5-1-01 6-1-01 7-1-01

This is I suppose, a sort of diary entry, and this information is added to another table to provide the appointment side of things. This has to be done as a seperate item for each time that the event occurs as the dates will be different.
I will send an e-mail to you George with an attachment containig more helpful information. I am sorry raskew, but i don't know how to provide you with the information. I do appologise for not being more helpful, but I would appreciate if you could stick with it, as I do need to get this sorted and I appreciate your time so far. Thanks a lot

Scoffagus......

 

[merlin72]

Hello scoffagus,
I was trting to do something of this sort and came across this program. I have learned alot from it. It might help you out too. Good luck.

http://www.mvps.org/access/downloads/MiniCalendar.zip

Regards
Jim.