Automatically entering data in a subform

  • Thread starter Thread starter Geoff
  • Start date Start date
G

Geoff

Hi everyone,

I haven't used Access for a while and now I can't remember basic stuff and
I'm going nuts and getting very frustrated with myself, so would be grateful
for some help please.

At the moment I have a form, and a subform within it. Each time I go to a
new record on the main form, I want the subform to already show 7 empty
records, apart from the list of days of the week (then several blank
numerical fields per day). So I guess I want those 7 records to be created
on the subform as soon as I create a new record on the main form. In fact it
wouldn't matter if all the 7 records in the subform weren't there initially
when I created a new record in the main form, but when I create a new record
in the subform I'd like the next day of the week to appear in the first
field automatically.

I've tried having a list box linked to a table that just has one field in
where I listed the days of the week, but I can't get that to work. In fact
I've tried several approaches but I'm honestly getting very frustrated with
it. I do so far have the main form linked to one table, and the subform
linked to another table, with one field linking the 2 tables, and that part
seems to work ok.

I've searched groups by the way to find this answer but I haven't found any
answer that I understand yet, hence writing this.

Thanks for any help,
Geoff.
 
At the moment I have a form, and a subform within it. Each time I go to a
new record on the main form, I want the subform to already show 7 empty
records, apart from the list of days of the week (then several blank
numerical fields per day). So I guess I want those 7 records to be created
on the subform as soon as I create a new record on the main form. In fact it
wouldn't matter if all the 7 records in the subform weren't there initially
when I created a new record in the main form, but when I create a new record
in the subform I'd like the next day of the week to appear in the first
field automatically.
Click to expand...

Empty "placeholder" records are generally not considered a good idea:
it's all too easy to have seven records stored in the table with one
or more of them empty of any real data! How are you storing the "days
of the week" in your subform's table? As text ("Sunday", "Monday"),
numbers 1-7, or (what I'd suggest) Date/Time values?

What I would suggest is that you have:

- A Date/Time field in the table
- A starting date for the week in a control (possibly bound, more
likely unbound) on the mainform
- A small table with seven rows and one Integer field filled with
values 1 to 7; let's call the table tblDays and the field DayNum. If
you wish, have a second text field with the day names.

Base the Subform on a Query including your current subform's table and
the seven row table, with NO join line. Display the day number (and/or
the day name) on the subform.

In the Subform's BeforeInsert event (which will fire whenever you
start typing data into one of the table fields on the subform), put
code like

Private Sub Form_BeforeInsert(Cancel as Integer)
Me!txtDatefield = DateAdd("d", Me!DayNum - 1, Parent!txtStartDate)
End Sub

This will add DayNum days to the mainform control's date and insert
that value into the date field on the subform.
 
John Vinson said:
Empty "placeholder" records are generally not considered a good idea:
it's all too easy to have seven records stored in the table with one
or more of them empty of any real data! How are you storing the "days
of the week" in your subform's table? As text ("Sunday", "Monday"),
numbers 1-7, or (what I'd suggest) Date/Time values?
Click to expand...
<snip>


Thanks for your reply John. Just to answer your question, I've tried storing
the day as both text and numbers, and tried various ways of doing it but
nothing works.

Later on I'll go through your instructions in a seperate test database and
then once I've built it up as you suggest, hopefully it'll become clearer in
my mind what you're suggesting I do. I understand each part on it's own but
I need to put it all together, then hopefully it'll click. :) Hopefully
then I'll be able to put it into my other database.

Thanks again,
Geoff.
 
Hi again John,

I'm totally stuck I'm afraid and feel pretty stupid. :(

I've basically done what you suggested but I am unclear on some of the code
(what parts do I substitute with field names? What names exactly?) and also
I'm not sure what you mean by inserting a control on the main form. It's
probably going to be clearer if I just tell you what I have at the moment...

I have a table called 'tbldays', with 2 fields as you suggested called
daynum and dayname, and 7 rows with days of the week and numbers 1 to 7 in.
I have a table called 'weeks', with several non-relevant fields but one that
links to the subform called weekstarting (date/time field).
I have a table called 'days', with several non-relevant fields but one
called weekstarting (date/time) and one called day (date/time).
I have the main form based on the table 'weeks'.
I have a subform based on the query you suggested, which has in it the
'days' and 'tbldays' tables, with no connecting lines.
The 'link child fields' and 'link master fields' setting in the subform are
both set to 'weekstarting'.

I hope that all makes sense, and I'd appreciate it if you could help me sort
this out please. I have to say that I'm very angry with myself because I
used to do all this without a problem several years ago, and it just isn't
coming back to me at all and it's extremely aggravating.

Cheers,
Geoff.
 
Back
Top