Automatically populate NEW RECORDS in Subform(for field not Linked

[babs]

I have a Main form - (Overall purposeof Both forms combined is to Scheduling
jobs for the Upcoming week)
Main form has WEEK End date manually put in(Always a Sun-they don't work
Sunday) , and Man Name selected from Dropdown

As of right now in the subform the user has to type in all of the dates for
that week
ex. Week End is 8/9/09(put in on Main form)-all just have it infor now
8/9/09-auto put in Like it should

For the actual WORKDATE User has to put in on subform the sched. to work ie.
8/3/09, 8/4/09,8/5/09,8/6/09,8/7/09,8/8/09

When the user puts in the Week End Date on the Main Form - I would like 6
Records automatically ADDED with the Workdate put in ON the Subform- then the
scheduler can input what actual jobs they are doing on that day
Thinking there should be some VB code to put in on the After Update event of
the Week End date on the Main form

Not sure what code to Insert Records into the Subform.

thanks sooo much,
Barb

 

[SuzyQ]

check out the insert into sql statement and the dateadd function for
increasing your weekend date by a specified number of days. Put that in a
loop from 1 -5 for the days you want to add.

Are you verifying that the user only enters a Sunday date? What would
happen if the user enters a date other than Sunday?

 

[babs]

yes,
they only put in a Sunday date-

thinking I would add an Event procedure to the after update of the WEEK End
date on the main form-

Not sure what the exact code should be to for the Do Loop to add the record
and put in the Mon-Sat dates prior???

Thanks so much for your help,
BARb

 

[SuzyQ]

Yes, the after_update event is where you could try the following air code

'START
dim dteWeedday as date
dim i as integer

for i = 1 to 6 '(Monday to Saturday)
dteWeekday = dateadd('d',me.SundayDate,i) 'increment sunday by i days
strSQL = "insert into [detailTable] ([dateField], [keyField]) Values ("
& dteWeekDay & ", " & me.keyFieldValue & ")"

DoCmd.RunSQL (strSQL)
next i
'END

 

[babs]

Is the keyfield value the primary key of the detail table??????

A few other points:

1. The strSQL variable should be declared:

Dim strSQL As String

2. The date value should be delimited with the # character when building the
string expression.

3. Its best to format the date value in an internationally unambiguous
format such as the ISO standard of YYYY-MM-DD, so:

strSQL = _
"INSERT INTO [detailTable] ([dateField], [keyField]) VALUES (#" & _
FORMAT(dteWeekDay,"yyyy-mm-dd") & "#," & Me.keyFieldValue & ")"

This assumes that the keyField is a number data type of course. If it’s a
text data type:

strSQL = _
"INSERT INTO [detailTable] ([dateField], [keyField]) VALUES (#" & _
FORMAT(dteWeekDay,"yyyy-mm-dd") & "#,""" & Me.keyFieldValue & """)"

4. The subform will need to be requeried after inserting the rows for them
to show:

Me.NameOfSubFormControl.Requery

Ken Sheridan
Stafford, England

Yes, the after_update event is where you could try the following air code

'START
dim dteWeedday as date
dim i as integer

for i = 1 to 6 '(Monday to Saturday)
dteWeekday = dateadd('d',me.SundayDate,i) 'increment sunday by i days
strSQL = "insert into [detailTable] ([dateField], [keyField]) Values ("
& dteWeekDay & ", " & me.keyFieldValue & ")"

DoCmd.RunSQL (strSQL)
next i
'END

yes,
they only put in a Sunday date-

[quoted text clipped - 38 lines]

thanks sooo much,
Barb

 

[babs]

or is the keyfield - the LInked field between main and subform

A few other points:

1. The strSQL variable should be declared:

Dim strSQL As String

2. The date value should be delimited with the # character when building the
string expression.

3. Its best to format the date value in an internationally unambiguous
format such as the ISO standard of YYYY-MM-DD, so:

strSQL = _
"INSERT INTO [detailTable] ([dateField], [keyField]) VALUES (#" & _
FORMAT(dteWeekDay,"yyyy-mm-dd") & "#," & Me.keyFieldValue & ")"

This assumes that the keyField is a number data type of course. If it’s a
text data type:

strSQL = _
"INSERT INTO [detailTable] ([dateField], [keyField]) VALUES (#" & _
FORMAT(dteWeekDay,"yyyy-mm-dd") & "#,""" & Me.keyFieldValue & """)"

4. The subform will need to be requeried after inserting the rows for them
to show:

Me.NameOfSubFormControl.Requery

Ken Sheridan
Stafford, England

Yes, the after_update event is where you could try the following air code

'START
dim dteWeedday as date
dim i as integer

for i = 1 to 6 '(Monday to Saturday)
dteWeekday = dateadd('d',me.SundayDate,i) 'increment sunday by i days
strSQL = "insert into [detailTable] ([dateField], [keyField]) Values ("
& dteWeekDay & ", " & me.keyFieldValue & ")"

DoCmd.RunSQL (strSQL)
next i
'END

yes,
they only put in a Sunday date-

[quoted text clipped - 38 lines]

thanks sooo much,
Barb

 

[SuzyQ]

The key field is the primary key in the parent table (the link between the
main table and the detail table)

Is the keyfield value the primary key of the detail table??????

A few other points:

1. The strSQL variable should be declared:

Dim strSQL As String

2. The date value should be delimited with the # character when building the
string expression.

3. Its best to format the date value in an internationally unambiguous
format such as the ISO standard of YYYY-MM-DD, so:

strSQL = _
"INSERT INTO [detailTable] ([dateField], [keyField]) VALUES (#" & _
FORMAT(dteWeekDay,"yyyy-mm-dd") & "#," & Me.keyFieldValue & ")"

This assumes that the keyField is a number data type of course. If it’s a
text data type:

strSQL = _
"INSERT INTO [detailTable] ([dateField], [keyField]) VALUES (#" & _
FORMAT(dteWeekDay,"yyyy-mm-dd") & "#,""" & Me.keyFieldValue & """)"

4. The subform will need to be requeried after inserting the rows for them
to show:

Me.NameOfSubFormControl.Requery

Ken Sheridan
Stafford, England

Yes, the after_update event is where you could try the following air code

'START
dim dteWeedday as date
dim i as integer

for i = 1 to 6 '(Monday to Saturday)
dteWeekday = dateadd('d',me.SundayDate,i) 'increment sunday by i days
strSQL = "insert into [detailTable] ([dateField], [keyField]) Values ("
& dteWeekDay & ", " & me.keyFieldValue & ")"

DoCmd.RunSQL (strSQL)
next i
'END

yes,
they only put in a Sunday date-
[quoted text clipped - 38 lines]
thanks sooo much,
Barb

 

[SuzyQ]

thanks for clarifying... I wrote it rather quickly and left out some details.

A few other points:

1. The strSQL variable should be declared:

Dim strSQL As String

2. The date value should be delimited with the # character when building the
string expression.

3. Its best to format the date value in an internationally unambiguous
format such as the ISO standard of YYYY-MM-DD, so:

strSQL = _
"INSERT INTO [detailTable] ([dateField], [keyField]) VALUES (#" & _
FORMAT(dteWeekDay,"yyyy-mm-dd") & "#," & Me.keyFieldValue & ")"

This assumes that the keyField is a number data type of course. If it’s a
text data type:

strSQL = _
"INSERT INTO [detailTable] ([dateField], [keyField]) VALUES (#" & _
FORMAT(dteWeekDay,"yyyy-mm-dd") & "#,""" & Me.keyFieldValue & """)"

4. The subform will need to be requeried after inserting the rows for them
to show:

Me.NameOfSubFormControl.Requery

Ken Sheridan
Stafford, England

Yes, the after_update event is where you could try the following air code

'START
dim dteWeedday as date
dim i as integer

for i = 1 to 6 '(Monday to Saturday)
dteWeekday = dateadd('d',me.SundayDate,i) 'increment sunday by i days
strSQL = "insert into [detailTable] ([dateField], [keyField]) Values ("
& dteWeekDay & ", " & me.keyFieldValue & ")"

DoCmd.RunSQL (strSQL)
next i
'END

yes,
they only put in a Sunday date-

[quoted text clipped - 38 lines]

thanks sooo much,
Barb

 

[babs]

It seems to be adding the records to the table that the subform is based
on(good!)
but I also need to have it insert with the New 6 records the Man Name that
was selected on the Main form - into each of the 6 records Below I have noted
where i attmepted to put in the code for that not sure why it is not getting
in there (it worked for the ONE record - when attache to after update of the
cboManname- but need it added to all 6 records

Below is the code I haveIknow i shouldn't use date as a field name but the
database was already made - seems to be okay for now)

Private Sub Command12_Click()
'START
Dim dteWeedday As Date
Dim i As Integer
Dim strSQL As String

For i = 1 To 6 '(Monday to Saturday)
dteWeekday = DateAdd("d", Me.txtdate, i) 'increment sunday by i days
strSQL = "INSERT INTO [TimeCardMDJEFF] ([workdate], [date]) VALUES (#" &
Format(dteWeekday, "yyyy-mm-dd") & "#," & Me.Date & ")"
DoCmd.RunSQL (strSQL)
datbarbTimeCardMDJEFFSubform2.Form!txtMANNAME = Me.cboman.Column(0)
'ABOVE LINE TRYING TO GRAB WHAT SELECTED IN COMBOBOX ON MAINFORM TO PUT INTO
SUBFORM WITH NEW 6 RECORDS????
Next i
'END
Me.datbarbTimeCardMDJEFFSubform2.Requery

End Sub

Thanks sooo much - I feel really close!!! thanks,
Barb

Usually both; it’s the name of the primary key column of the table underlying
the parent form and of the foreign key column in the table underlying the
subform which references the key of the parent form's table. The names of
the two columns can differ of course, but more often than not the same name
is used. They will be the LinkMasterFields and LinkChildFields properties of
the subform control.

Ken Sheridan
Stafford, England

or is the keyfield - the LInked field between main and subform

A few other points:

[quoted text clipped - 47 lines]

thanks sooo much,
Barb

 

[babs]

Please ignore previous post - was able to get it with new code
my key field is the wonderful Date field for some reason the date field was
coming in like a serial number-prob. since not formatted right after I added
the formatting below for date(which is the represents week end date) I get
7/3/1894 inserted on table that subform is based off of when main form week
end date is 9/6/2009- workdate looks good

where is my code below messed up for that date field???
Private Sub Command12_Click()
'START
Dim dteWeedday As Date
Dim i As Integer
Dim strSQL As String

For i = 1 To 6 '(Monday to Saturday)
dteWeekday = DateAdd("d", Me.txtdate, i) 'increment sunday by i days
strSQL = "INSERT INTO [TimeCardMDJEFF] ([workdate], [date],[man name])
VALUES (#" & Format(dteWeekday, "m-d-yyyy") & "#," & Format(Me.Date,
"m-d-yyyy") & ",""" & Me.cboman.Column(0) & """)"
DoCmd.RunSQL (strSQL)
Next i
'END
Me.datbarbTimeCardMDJEFFSubform2.Requery

End Sub

thanks a ton again getting CLOSER!!!
barb

Usually both; it’s the name of the primary key column of the table underlying
the parent form and of the foreign key column in the table underlying the
subform which references the key of the parent form's table. The names of
the two columns can differ of course, but more often than not the same name
is used. They will be the LinkMasterFields and LinkChildFields properties of
the subform control.

Ken Sheridan
Stafford, England

or is the keyfield - the LInked field between main and subform

A few other points:

[quoted text clipped - 47 lines]

thanks sooo much,
Barb

 

[babs]

Sorry -getting very excited -
New Problem- see new records in table subform base off of but can't see them
in subformwhen -on Main form
Link master Field
Link child field
Date;Man Name
date;man name
seem to be okay not sure why not visible???

okay seem to have inserted the Date field into table subform is based off of
with correct formatting with code below-yeah:
Private Sub Command12_Click()
'START
Dim dteWeedday As Date
Dim i As Integer
Dim strSQL As String

For i = 1 To 6 '(Monday to Saturday)
dteWeekday = DateAdd("d", Me.txtdate, i) 'increment sunday by i days
strSQL = "INSERT INTO [TimeCardMDJEFF] ([workdate],[date],[man name]) VALUES
(#" & Format(dteWeekday, "m-d-yyyy") & "#," & "#" & Format(Me.Date,
"m-d-yyyy") & "#,""" & Me.cboman.Column(0) & """)"
DoCmd.RunSQL (strSQL)
Next i
'END
Me.datbarbTimeCardMDJEFFSubform2.Requery

End Sub

Please ignore previous post - was able to get it with new code
my key field is the wonderful Date field for some reason the date field was
coming in like a serial number-prob. since not formatted right after I added
the formatting below for date(which is the represents week end date) I get
7/3/1894 inserted on table that subform is based off of when main form week
end date is 9/6/2009- workdate looks good

where is my code below messed up for that date field???
Private Sub Command12_Click()
'START
Dim dteWeedday As Date
Dim i As Integer
Dim strSQL As String

For i = 1 To 6 '(Monday to Saturday)
dteWeekday = DateAdd("d", Me.txtdate, i) 'increment sunday by i days
strSQL = "INSERT INTO [TimeCardMDJEFF] ([workdate], [date],[man name])
VALUES (#" & Format(dteWeekday, "m-d-yyyy") & "#," & Format(Me.Date,
"m-d-yyyy") & ",""" & Me.cboman.Column(0) & """)"
DoCmd.RunSQL (strSQL)
Next i
'END
Me.datbarbTimeCardMDJEFFSubform2.Requery

End Sub

thanks a ton again getting CLOSER!!!
barb

Usually both; it’s the name of the primary key column of the table underlying
the parent form and of the foreign key column in the table underlying the
subform which references the key of the parent form's table. The names of
the two columns can differ of course, but more often than not the same name
is used. They will be the LinkMasterFields and LinkChildFields properties of
the subform control.

Ken Sheridan
Stafford, England

or is the keyfield - the LInked field between main and subform

A few other points:

[quoted text clipped - 47 lines]
thanks sooo much,
Barb

 

[babs]

GOT IT!!!! THANKS AGAIN SOO MUCH
I must have change properties on the subform for data entry from yes to no
don't really understand then when set to NO records show up but it works!!

thanks again,
barb

Sorry -getting very excited -
New Problem- see new records in table subform base off of but can't see them
in subformwhen -on Main form
Link master Field
Link child field
Date;Man Name
date;man name
seem to be okay not sure why not visible???

okay seem to have inserted the Date field into table subform is based off of
with correct formatting with code below-yeah:
Private Sub Command12_Click()
'START
Dim dteWeedday As Date
Dim i As Integer
Dim strSQL As String

For i = 1 To 6 '(Monday to Saturday)
dteWeekday = DateAdd("d", Me.txtdate, i) 'increment sunday by i days
strSQL = "INSERT INTO [TimeCardMDJEFF] ([workdate],[date],[man name]) VALUES
(#" & Format(dteWeekday, "m-d-yyyy") & "#," & "#" & Format(Me.Date,
"m-d-yyyy") & "#,""" & Me.cboman.Column(0) & """)"
DoCmd.RunSQL (strSQL)
Next i
'END
Me.datbarbTimeCardMDJEFFSubform2.Requery

End Sub

Please ignore previous post - was able to get it with new code
my key field is the wonderful Date field for some reason the date field was
coming in like a serial number-prob. since not formatted right after I added
the formatting below for date(which is the represents week end date) I get
7/3/1894 inserted on table that subform is based off of when main form week
end date is 9/6/2009- workdate looks good

where is my code below messed up for that date field???
Private Sub Command12_Click()
'START
Dim dteWeedday As Date
Dim i As Integer
Dim strSQL As String

For i = 1 To 6 '(Monday to Saturday)
dteWeekday = DateAdd("d", Me.txtdate, i) 'increment sunday by i days
strSQL = "INSERT INTO [TimeCardMDJEFF] ([workdate], [date],[man name])
VALUES (#" & Format(dteWeekday, "m-d-yyyy") & "#," & Format(Me.Date,
"m-d-yyyy") & ",""" & Me.cboman.Column(0) & """)"
DoCmd.RunSQL (strSQL)
Next i
'END
Me.datbarbTimeCardMDJEFFSubform2.Requery

End Sub

thanks a ton again getting CLOSER!!!
barb

Usually both; it’s the name of the primary key column of the table underlying
the parent form and of the foreign key column in the table underlying the
subform which references the key of the parent form's table. The names of
the two columns can differ of course, but more often than not the same name
is used. They will be the LinkMasterFields and LinkChildFields properties of
the subform control.

Ken Sheridan
Stafford, England

babs wrote:
or is the keyfield - the LInked field between main and subform

A few other points:

[quoted text clipped - 47 lines]
thanks sooo much,
Barb

 

[John W. Vinson]

I must have change properties on the subform for data entry from yes to no
don't really understand then when set to NO records show up but it works!!

The "Data Entry" property can be confusing. It means "This form can be used
only to enter new records, old records will be concealed". You can enter data
on a form with Data Entry set to No (so long as the form's Allow Additions
property is Yes); you certainly don't need to set Data Entry to Yes.

 

[babs]

Hi John- Added some new records - When I add a new record on the main form
and select a NEW name to schedule i- the new name shows up in the combo box -
BUT when I go to insert the records - it grabs the name from the LASt record
that I put in - not sure what code I need to add and on what Place - on combo
box, on form or on command button to insert records to make it the Most
present name selected in combo box???

thanks soo much,
barb

of course need within the next half hour if possible