Subform Default Records

[GaryS]

Hi- I have a form, frmModelInfo bound to tblModel, and a subform,
tblModelinfoSubform1, bound to tblModelInfo. For every new record entered in
frmModelInfo I would like to get 5 default records displayed in the subform,
with the default values in certain text boxes on each record. The particular
text box is txtTerm, and I would like to have 24, 30, 36, 39, and 48 set up
as defaults. How could I do this? Thanks so much.

 

[Graham Mandeno]

Hi Gary

Is txtTerm bound to a field that is related to some other table? The five
numbers you mention sound like they are primary key values in another table.
If so, then I would add a boolean (yes/no) field "AddByDefault" to that
table and execute an append query in your form's AfterInsert event.

Your code would look something like this:

Private Sub Form_AfterInsert()
Dim sSQL As String
sSQL = "Insert into tblModelInfo select " _
& Me!ModelID & " as ModelID, TermID as Term " _
& "from Terms where AddByDefault<>0;"
CurrentDb.Execute sSQL, dbFailOnError
End Sub

Of course, you would need to tweak the above to suit your own table and
field names.

If my assumption is wring, and these are just five arbitrary values, then
you should store then in a table and not hard-code them in your VBA. That
will make it much easier to change the values in the future if necessary.

Create a table, "tblDefaultTerms" with one numeric field "Term". Add in
five records, one for each default value. Now your AfterInsert code should
look like this:

Private Sub Form_AfterInsert()
Dim sSQL As String
sSQL = "Insert into tblModelInfo select " _
& Me!ModelID & " as ModelID, Term " _
& "from tblDefaultTerms;"
CurrentDb.Execute sSQL, dbFailOnError
End Sub

 

[GaryS]

Graham,

TxtTerm is bound to the field "Term" in tblModelIfo, and it is part of the
primary key, but not a foreign key. This is for an auto leasing database
that I created. I currently manually set up each leasing Term, and 24
months, 30 months, 36 months, 39 months, and 48 months, are the most common
terms. Each term becomes a sub record. I just want to have default records
(Terms) already setup in the subform like this:
[Term] [Resid] [Rate] and so on
24
30
36
39
48

I realize these could change, that's why they are only defaults, and I would
only ad them with a command button, not actually make them automatic. At
some point I could ad a tblDefaultTerms, but that's not necessary right now.

Gary in Michigan, USA

Hi Gary

Is txtTerm bound to a field that is related to some other table? The five
numbers you mention sound like they are primary key values in another table.
If so, then I would add a boolean (yes/no) field "AddByDefault" to that
table and execute an append query in your form's AfterInsert event.

Your code would look something like this:

Private Sub Form_AfterInsert()
Dim sSQL As String
sSQL = "Insert into tblModelInfo select " _
& Me!ModelID & " as ModelID, TermID as Term " _
& "from Terms where AddByDefault<>0;"
CurrentDb.Execute sSQL, dbFailOnError
End Sub

Of course, you would need to tweak the above to suit your own table and
field names.

If my assumption is wring, and these are just five arbitrary values, then
you should store then in a table and not hard-code them in your VBA. That
will make it much easier to change the values in the future if necessary.

Create a table, "tblDefaultTerms" with one numeric field "Term". Add in
five records, one for each default value. Now your AfterInsert code should
look like this:

Private Sub Form_AfterInsert()
Dim sSQL As String
sSQL = "Insert into tblModelInfo select " _
& Me!ModelID & " as ModelID, Term " _
& "from tblDefaultTerms;"
CurrentDb.Execute sSQL, dbFailOnError
End Sub

--
Good Luck

Graham Mandeno [Access MVP]
Auckland, New Zealand

Hi- I have a form, frmModelInfo bound to tblModel, and a subform,
tblModelinfoSubform1, bound to tblModelInfo. For every new record entered
in
frmModelInfo I would like to get 5 default records displayed in the
subform,
with the default values in certain text boxes on each record. The
particular
text box is txtTerm, and I would like to have 24, 30, 36, 39, and 48 set
up
as defaults. How could I do this? Thanks so much.

 

[GaryS]

Actually, after re-reading your post, I'll play with the tblDefaultTerms and
see how it works. Thanks
--
Gary in Michigan, USA

Hi Gary

Is txtTerm bound to a field that is related to some other table? The five
numbers you mention sound like they are primary key values in another table.
If so, then I would add a boolean (yes/no) field "AddByDefault" to that
table and execute an append query in your form's AfterInsert event.

Your code would look something like this:

Private Sub Form_AfterInsert()
Dim sSQL As String
sSQL = "Insert into tblModelInfo select " _
& Me!ModelID & " as ModelID, TermID as Term " _
& "from Terms where AddByDefault<>0;"
CurrentDb.Execute sSQL, dbFailOnError
End Sub

Of course, you would need to tweak the above to suit your own table and
field names.

If my assumption is wring, and these are just five arbitrary values, then
you should store then in a table and not hard-code them in your VBA. That
will make it much easier to change the values in the future if necessary.

Create a table, "tblDefaultTerms" with one numeric field "Term". Add in
five records, one for each default value. Now your AfterInsert code should
look like this:

Private Sub Form_AfterInsert()
Dim sSQL As String
sSQL = "Insert into tblModelInfo select " _
& Me!ModelID & " as ModelID, Term " _
& "from tblDefaultTerms;"
CurrentDb.Execute sSQL, dbFailOnError
End Sub

--
Good Luck

Graham Mandeno [Access MVP]
Auckland, New Zealand

Hi- I have a form, frmModelInfo bound to tblModel, and a subform,
tblModelinfoSubform1, bound to tblModelInfo. For every new record entered
in
frmModelInfo I would like to get 5 default records displayed in the
subform,
with the default values in certain text boxes on each record. The
particular
text box is txtTerm, and I would like to have 24, 30, 36, 39, and 48 set
up
as defaults. How could I do this? Thanks so much.

 

[GaryS]

Oops! I get the following error when I place your code in a command button
instead of the Afterinsert Event:

CurrentDb.Execute sSQL, dbFailOnError dbfail on error 128:

I'll keep playing, but further insight would be appreciated. Thanks!

--
Gary in Michigan, USA

Hi Gary

Is txtTerm bound to a field that is related to some other table? The five
numbers you mention sound like they are primary key values in another table.
If so, then I would add a boolean (yes/no) field "AddByDefault" to that
table and execute an append query in your form's AfterInsert event.

Your code would look something like this:

Private Sub Form_AfterInsert()
Dim sSQL As String
sSQL = "Insert into tblModelInfo select " _
& Me!ModelID & " as ModelID, TermID as Term " _
& "from Terms where AddByDefault<>0;"
CurrentDb.Execute sSQL, dbFailOnError
End Sub

Of course, you would need to tweak the above to suit your own table and
field names.

If my assumption is wring, and these are just five arbitrary values, then
you should store then in a table and not hard-code them in your VBA. That
will make it much easier to change the values in the future if necessary.

Create a table, "tblDefaultTerms" with one numeric field "Term". Add in
five records, one for each default value. Now your AfterInsert code should
look like this:

Private Sub Form_AfterInsert()
Dim sSQL As String
sSQL = "Insert into tblModelInfo select " _
& Me!ModelID & " as ModelID, Term " _
& "from tblDefaultTerms;"
CurrentDb.Execute sSQL, dbFailOnError
End Sub

--
Good Luck

Graham Mandeno [Access MVP]
Auckland, New Zealand

Hi- I have a form, frmModelInfo bound to tblModel, and a subform,
tblModelinfoSubform1, bound to tblModelInfo. For every new record entered
in
frmModelInfo I would like to get 5 default records displayed in the
subform,
with the default values in certain text boxes on each record. The
particular
text box is txtTerm, and I would like to have 24, 30, 36, 39, and 48 set
up
as defaults. How could I do this? Thanks so much.

 

[Graham Mandeno]

Hi Gary

Yes, I would definitely recommend the table approach. It's much easier to
change data than it is to change code!

If your new record has not yet been saved, then there will be no record in
tblModel to which the new records in tblModelInfo are related. In
Form_AfterInsert this was not a problem because you know that the record
*has* just been saved, but with a command button you need to do a manual
check:

If Me.NewRecord then
If Me.Dirty Then
DoCmd.RunCommand acCmdSaveRecord
Else
MsgBox "Please enter some data to save"
Exit Sub
End If
End If
.... rest of the code

--
Good Luck

Graham Mandeno [Access MVP]
Auckland, New Zealand

Oops! I get the following error when I place your code in a command
button
instead of the Afterinsert Event:

CurrentDb.Execute sSQL, dbFailOnError dbfail on error 128:

I'll keep playing, but further insight would be appreciated. Thanks!

--
Gary in Michigan, USA

Hi Gary

Is txtTerm bound to a field that is related to some other table? The
five
numbers you mention sound like they are primary key values in another
table.
If so, then I would add a boolean (yes/no) field "AddByDefault" to that
table and execute an append query in your form's AfterInsert event.

Your code would look something like this:

Private Sub Form_AfterInsert()
Dim sSQL As String
sSQL = "Insert into tblModelInfo select " _
& Me!ModelID & " as ModelID, TermID as Term " _
& "from Terms where AddByDefault<>0;"
CurrentDb.Execute sSQL, dbFailOnError
End Sub

Of course, you would need to tweak the above to suit your own table and
field names.

If my assumption is wring, and these are just five arbitrary values, then
you should store then in a table and not hard-code them in your VBA.
That
will make it much easier to change the values in the future if necessary.

Create a table, "tblDefaultTerms" with one numeric field "Term". Add in
five records, one for each default value. Now your AfterInsert code
should
look like this:

Private Sub Form_AfterInsert()
Dim sSQL As String
sSQL = "Insert into tblModelInfo select " _
& Me!ModelID & " as ModelID, Term " _
& "from tblDefaultTerms;"
CurrentDb.Execute sSQL, dbFailOnError
End Sub

--
Good Luck

Graham Mandeno [Access MVP]
Auckland, New Zealand

Hi- I have a form, frmModelInfo bound to tblModel, and a subform,
tblModelinfoSubform1, bound to tblModelInfo. For every new record
entered
in
frmModelInfo I would like to get 5 default records displayed in the
subform,
with the default values in certain text boxes on each record. The
particular
text box is txtTerm, and I would like to have 24, 30, 36, 39, and 48
set
up
as defaults. How could I do this? Thanks so much.

 

[GaryS]

Hi Graham,

That makes sense, but after adding the if statement I still get the
"dbfail on error=128" message on the last line of the code. Any other
thoughts?

Thanks!
--
Gary in Michigan, USA

Hi Gary

Yes, I would definitely recommend the table approach. It's much easier to
change data than it is to change code!

If your new record has not yet been saved, then there will be no record in
tblModel to which the new records in tblModelInfo are related. In
Form_AfterInsert this was not a problem because you know that the record
*has* just been saved, but with a command button you need to do a manual
check:

If Me.NewRecord then
If Me.Dirty Then
DoCmd.RunCommand acCmdSaveRecord
Else
MsgBox "Please enter some data to save"
Exit Sub
End If
End If
... rest of the code

--
Good Luck

Graham Mandeno [Access MVP]
Auckland, New Zealand

Oops! I get the following error when I place your code in a command
button
instead of the Afterinsert Event:

CurrentDb.Execute sSQL, dbFailOnError dbfail on error 128:

I'll keep playing, but further insight would be appreciated. Thanks!

--
Gary in Michigan, USA

Hi Gary

Is txtTerm bound to a field that is related to some other table? The
five
numbers you mention sound like they are primary key values in another
table.
If so, then I would add a boolean (yes/no) field "AddByDefault" to that
table and execute an append query in your form's AfterInsert event.

Your code would look something like this:

Private Sub Form_AfterInsert()
Dim sSQL As String
sSQL = "Insert into tblModelInfo select " _
& Me!ModelID & " as ModelID, TermID as Term " _
& "from Terms where AddByDefault<>0;"
CurrentDb.Execute sSQL, dbFailOnError
End Sub

Of course, you would need to tweak the above to suit your own table and
field names.

If my assumption is wring, and these are just five arbitrary values, then
you should store then in a table and not hard-code them in your VBA.
That
will make it much easier to change the values in the future if necessary.

Create a table, "tblDefaultTerms" with one numeric field "Term". Add in
five records, one for each default value. Now your AfterInsert code
should
look like this:

Private Sub Form_AfterInsert()
Dim sSQL As String
sSQL = "Insert into tblModelInfo select " _
& Me!ModelID & " as ModelID, Term " _
& "from tblDefaultTerms;"
CurrentDb.Execute sSQL, dbFailOnError
End Sub

--
Good Luck

Graham Mandeno [Access MVP]
Auckland, New Zealand

Hi- I have a form, frmModelInfo bound to tblModel, and a subform,
tblModelinfoSubform1, bound to tblModelInfo. For every new record
entered
in
frmModelInfo I would like to get 5 default records displayed in the
subform,
with the default values in certain text boxes on each record. The
particular
text box is txtTerm, and I would like to have 24, 30, 36, 39, and 48
set
up
as defaults. How could I do this? Thanks so much.

 

[GaryS]

Hi Graham,

Digging more I realize I forgot to mention that ModelID is a text field, so
I've now adjusted the code for that, but still having issues. Here is the
code I am using.

If Me.NewRecord Then
If Me.Dirty Then
DoCmd.RunCommand acCmdSaveRecord
Else
MsgBox "Please enter Model info before adding Terms"
Exit Sub
End If
End If

Dim sSQL As String
sSQL = "Insert into tblModelDetails select " _
& Chr$(34) & Me!ModelID & Chr$(34) & Chr$(34) & Me!ModelID & Chr$(34) & "
as ModelID, Term " _
& "from tblDefaultTerms;"

CurrentDb.Execute sSQL, dbFailOnError

End Sub

Now I get the following: Run-time error'3201':

You cannot add or change a record because a related record is required in
table 'tblModel'.

I checked the tblModel, and the new record is there, so I don't understand
the problem. I'd appreciate any further help. Thanks!


--
Gary in Michigan, USA

Hi Gary

Yes, I would definitely recommend the table approach. It's much easier to
change data than it is to change code!

If your new record has not yet been saved, then there will be no record in
tblModel to which the new records in tblModelInfo are related. In
Form_AfterInsert this was not a problem because you know that the record
*has* just been saved, but with a command button you need to do a manual
check:

If Me.NewRecord then
If Me.Dirty Then
DoCmd.RunCommand acCmdSaveRecord
Else
MsgBox "Please enter some data to save"
Exit Sub
End If
End If
... rest of the code

--
Good Luck

Graham Mandeno [Access MVP]
Auckland, New Zealand

Oops! I get the following error when I place your code in a command
button
instead of the Afterinsert Event:

CurrentDb.Execute sSQL, dbFailOnError dbfail on error 128:

I'll keep playing, but further insight would be appreciated. Thanks!

--
Gary in Michigan, USA

Hi Gary

Is txtTerm bound to a field that is related to some other table? The
five
numbers you mention sound like they are primary key values in another
table.
If so, then I would add a boolean (yes/no) field "AddByDefault" to that
table and execute an append query in your form's AfterInsert event.

Your code would look something like this:

Private Sub Form_AfterInsert()
Dim sSQL As String
sSQL = "Insert into tblModelInfo select " _
& Me!ModelID & " as ModelID, TermID as Term " _
& "from Terms where AddByDefault<>0;"
CurrentDb.Execute sSQL, dbFailOnError
End Sub

Of course, you would need to tweak the above to suit your own table and
field names.

If my assumption is wring, and these are just five arbitrary values, then
you should store then in a table and not hard-code them in your VBA.
That
will make it much easier to change the values in the future if necessary.

Create a table, "tblDefaultTerms" with one numeric field "Term". Add in
five records, one for each default value. Now your AfterInsert code
should
look like this:

Private Sub Form_AfterInsert()
Dim sSQL As String
sSQL = "Insert into tblModelInfo select " _
& Me!ModelID & " as ModelID, Term " _
& "from tblDefaultTerms;"
CurrentDb.Execute sSQL, dbFailOnError
End Sub

--
Good Luck

Graham Mandeno [Access MVP]
Auckland, New Zealand

Hi- I have a form, frmModelInfo bound to tblModel, and a subform,
tblModelinfoSubform1, bound to tblModelInfo. For every new record
entered
in
frmModelInfo I would like to get 5 default records displayed in the
subform,
with the default values in certain text boxes on each record. The
particular
text box is txtTerm, and I would like to have 24, 30, 36, 39, and 48
set
up
as defaults. How could I do this? Thanks so much.

 

[GaryS]

Hi again Graham,

It does work now!! Here is the modified code.

If Me.NewRecord Then
If Me.Dirty Then
DoCmd.RunCommand acCmdSaveRecord
Else
MsgBox "Please enter Model info before adding Terms"
Exit Sub
End If
End If

Dim sSQL As String

sSQL = "Insert into tblModelDetails select " _
& Chr$(34) & Me!ModelID & Chr$(34) & " as ModelID, Term " _
& "from tblDefaultTerms;"

CurrentDb.Execute sSQL, dbFailOnError
Forms!frmModelinfo![tblModeldetails subform1].Requery

End Sub
--
Gary in Michigan, USA

Hi Gary

Yes, I would definitely recommend the table approach. It's much easier to
change data than it is to change code!

If your new record has not yet been saved, then there will be no record in
tblModel to which the new records in tblModelInfo are related. In
Form_AfterInsert this was not a problem because you know that the record
*has* just been saved, but with a command button you need to do a manual
check:

If Me.NewRecord then
If Me.Dirty Then
DoCmd.RunCommand acCmdSaveRecord
Else
MsgBox "Please enter some data to save"
Exit Sub
End If
End If
... rest of the code

--
Good Luck

Graham Mandeno [Access MVP]
Auckland, New Zealand

Oops! I get the following error when I place your code in a command
button
instead of the Afterinsert Event:

CurrentDb.Execute sSQL, dbFailOnError dbfail on error 128:

I'll keep playing, but further insight would be appreciated. Thanks!

--
Gary in Michigan, USA

Hi Gary

Is txtTerm bound to a field that is related to some other table? The
five
numbers you mention sound like they are primary key values in another
table.
If so, then I would add a boolean (yes/no) field "AddByDefault" to that
table and execute an append query in your form's AfterInsert event.

Your code would look something like this:

Private Sub Form_AfterInsert()
Dim sSQL As String
sSQL = "Insert into tblModelInfo select " _
& Me!ModelID & " as ModelID, TermID as Term " _
& "from Terms where AddByDefault<>0;"
CurrentDb.Execute sSQL, dbFailOnError
End Sub

Of course, you would need to tweak the above to suit your own table and
field names.

If my assumption is wring, and these are just five arbitrary values, then
you should store then in a table and not hard-code them in your VBA.
That
will make it much easier to change the values in the future if necessary.

Create a table, "tblDefaultTerms" with one numeric field "Term". Add in
five records, one for each default value. Now your AfterInsert code
should
look like this:

Private Sub Form_AfterInsert()
Dim sSQL As String
sSQL = "Insert into tblModelInfo select " _
& Me!ModelID & " as ModelID, Term " _
& "from tblDefaultTerms;"
CurrentDb.Execute sSQL, dbFailOnError
End Sub

--
Good Luck

Graham Mandeno [Access MVP]
Auckland, New Zealand

Hi- I have a form, frmModelInfo bound to tblModel, and a subform,
tblModelinfoSubform1, bound to tblModelInfo. For every new record
entered
in
frmModelInfo I would like to get 5 default records displayed in the
subform,
with the default values in certain text boxes on each record. The
particular
text box is txtTerm, and I would like to have 24, 30, 36, 39, and 48
set
up
as defaults. How could I do this? Thanks so much.

 

[GaryS]

Oops! And I meant to say THANKS SO MUCH!!!
--
Gary in Michigan, USA

Hi Gary

Yes, I would definitely recommend the table approach. It's much easier to
change data than it is to change code!

If your new record has not yet been saved, then there will be no record in
tblModel to which the new records in tblModelInfo are related. In
Form_AfterInsert this was not a problem because you know that the record
*has* just been saved, but with a command button you need to do a manual
check:

If Me.NewRecord then
If Me.Dirty Then
DoCmd.RunCommand acCmdSaveRecord
Else
MsgBox "Please enter some data to save"
Exit Sub
End If
End If
... rest of the code

--
Good Luck

Graham Mandeno [Access MVP]
Auckland, New Zealand

Oops! I get the following error when I place your code in a command
button
instead of the Afterinsert Event:

CurrentDb.Execute sSQL, dbFailOnError dbfail on error 128:

I'll keep playing, but further insight would be appreciated. Thanks!

--
Gary in Michigan, USA

Hi Gary

Is txtTerm bound to a field that is related to some other table? The
five
numbers you mention sound like they are primary key values in another
table.
If so, then I would add a boolean (yes/no) field "AddByDefault" to that
table and execute an append query in your form's AfterInsert event.

Your code would look something like this:

Private Sub Form_AfterInsert()
Dim sSQL As String
sSQL = "Insert into tblModelInfo select " _
& Me!ModelID & " as ModelID, TermID as Term " _
& "from Terms where AddByDefault<>0;"
CurrentDb.Execute sSQL, dbFailOnError
End Sub

Of course, you would need to tweak the above to suit your own table and
field names.

If my assumption is wring, and these are just five arbitrary values, then
you should store then in a table and not hard-code them in your VBA.
That
will make it much easier to change the values in the future if necessary.

Create a table, "tblDefaultTerms" with one numeric field "Term". Add in
five records, one for each default value. Now your AfterInsert code
should
look like this:

Private Sub Form_AfterInsert()
Dim sSQL As String
sSQL = "Insert into tblModelInfo select " _
& Me!ModelID & " as ModelID, Term " _
& "from tblDefaultTerms;"
CurrentDb.Execute sSQL, dbFailOnError
End Sub

--
Good Luck

Graham Mandeno [Access MVP]
Auckland, New Zealand

Hi- I have a form, frmModelInfo bound to tblModel, and a subform,
tblModelinfoSubform1, bound to tblModelInfo. For every new record
entered
in
frmModelInfo I would like to get 5 default records displayed in the
subform,
with the default values in certain text boxes on each record. The
particular
text box is txtTerm, and I would like to have 24, 30, 36, 39, and 48
set
up
as defaults. How could I do this? Thanks so much.

 

[Graham Mandeno]

Hi Gary

You're very welcome! I'm sorry - we were sound asleep in my part of the
world while you were wrestling with this earlier today

Just a tip: you could have inserted Debug.Print sSql or MsgBox sSql before
the CurrentDb.Execute statement and that would have helped you to spot the
error in your SQL statement.

--
Good Luck

Graham Mandeno [Access MVP]
Auckland, New Zealand

Oops! And I meant to say THANKS SO MUCH!!!
--
Gary in Michigan, USA

Hi Gary

Yes, I would definitely recommend the table approach. It's much easier
to
change data than it is to change code!

If your new record has not yet been saved, then there will be no record
in
tblModel to which the new records in tblModelInfo are related. In
Form_AfterInsert this was not a problem because you know that the record
*has* just been saved, but with a command button you need to do a manual
check:

If Me.NewRecord then
If Me.Dirty Then
DoCmd.RunCommand acCmdSaveRecord
Else
MsgBox "Please enter some data to save"
Exit Sub
End If
End If
... rest of the code

--
Good Luck

Graham Mandeno [Access MVP]
Auckland, New Zealand

Oops! I get the following error when I place your code in a command
button
instead of the Afterinsert Event:

CurrentDb.Execute sSQL, dbFailOnError dbfail on error 128:

I'll keep playing, but further insight would be appreciated. Thanks!

--
Gary in Michigan, USA


:

Hi Gary

Is txtTerm bound to a field that is related to some other table? The
five
numbers you mention sound like they are primary key values in another
table.
If so, then I would add a boolean (yes/no) field "AddByDefault" to
that
table and execute an append query in your form's AfterInsert event.

Your code would look something like this:

Private Sub Form_AfterInsert()
Dim sSQL As String
sSQL = "Insert into tblModelInfo select " _
& Me!ModelID & " as ModelID, TermID as Term " _
& "from Terms where AddByDefault<>0;"
CurrentDb.Execute sSQL, dbFailOnError
End Sub

Of course, you would need to tweak the above to suit your own table
and
field names.

If my assumption is wring, and these are just five arbitrary values,
then
you should store then in a table and not hard-code them in your VBA.
That
will make it much easier to change the values in the future if
necessary.

Create a table, "tblDefaultTerms" with one numeric field "Term". Add
in
five records, one for each default value. Now your AfterInsert code
should
look like this:

Private Sub Form_AfterInsert()
Dim sSQL As String
sSQL = "Insert into tblModelInfo select " _
& Me!ModelID & " as ModelID, Term " _
& "from tblDefaultTerms;"
CurrentDb.Execute sSQL, dbFailOnError
End Sub

--
Good Luck

Graham Mandeno [Access MVP]
Auckland, New Zealand

Hi- I have a form, frmModelInfo bound to tblModel, and a subform,
tblModelinfoSubform1, bound to tblModelInfo. For every new record
entered
in
frmModelInfo I would like to get 5 default records displayed in the
subform,
with the default values in certain text boxes on each record. The
particular
text box is txtTerm, and I would like to have 24, 30, 36, 39, and 48
set
up
as defaults. How could I do this? Thanks so much.

 

[Mary]

I have a similar situation and can't get this code to work.

Main form oa_new (values I need are ID and pcuser)
subform SLogSub (values to update are EventID and UID)

For each record on SLogSub, I want the EventID to update with the value in
ID and UID to update with the value in pcuser.

Thank you,
Mary

Hi Gary

Is txtTerm bound to a field that is related to some other table? The five
numbers you mention sound like they are primary key values in another table.
If so, then I would add a boolean (yes/no) field "AddByDefault" to that
table and execute an append query in your form's AfterInsert event.

Your code would look something like this:

Private Sub Form_AfterInsert()
Dim sSQL As String
sSQL = "Insert into tblModelInfo select " _
& Me!ModelID & " as ModelID, TermID as Term " _
& "from Terms where AddByDefault<>0;"
CurrentDb.Execute sSQL, dbFailOnError
End Sub

Of course, you would need to tweak the above to suit your own table and
field names.

If my assumption is wring, and these are just five arbitrary values, then
you should store then in a table and not hard-code them in your VBA. That
will make it much easier to change the values in the future if necessary.

Create a table, "tblDefaultTerms" with one numeric field "Term". Add in
five records, one for each default value. Now your AfterInsert code should
look like this:

Private Sub Form_AfterInsert()
Dim sSQL As String
sSQL = "Insert into tblModelInfo select " _
& Me!ModelID & " as ModelID, Term " _
& "from tblDefaultTerms;"
CurrentDb.Execute sSQL, dbFailOnError
End Sub

--
Good Luck

Graham Mandeno [Access MVP]
Auckland, New Zealand

Hi- I have a form, frmModelInfo bound to tblModel, and a subform,
tblModelinfoSubform1, bound to tblModelInfo. For every new record entered
in
frmModelInfo I would like to get 5 default records displayed in the
subform,
with the default values in certain text boxes on each record. The
particular
text box is txtTerm, and I would like to have 24, 30, 36, 39, and 48 set
up
as defaults. How could I do this? Thanks so much.