Autofill ID fields

[Bob M]

I am using Microsoft Access 2002 SP3. I have one main table named Issues
and several supporting tables all related on the ID field. The Issues table
ID field has an Autonumber to populate the field when a new record is
created. What I would like to accomplish is to have the supporting table’s
auto fill the ID field using the last ID created in the Issues table.

On my Issues form I have a combo box that depending on the selection
will open the corresponding form to the particular table. When the form
opens I want the Issues form to close and the ID field to auto fill with the
last record ID in the Issues table. After I placed a request in this forum
I now have the ability to open a new record that will autofill the ID field.
My goal is to have the ability to create one or more records in the secondary
tables created with the same linking ID.

Any and all assistance is greatly appreciated,
Bob

Below is what I currently have:

Private Sub Combo32_Change()

DoCmd.RunCommand acCmdSaveRecord
Dim strPushID As Integer
strPushID = Me.[ID]

If (Forms!FrmIssues!Combo32 = "Ordering") Then
DoCmd.OpenForm "FrmOrder", acNormal, "", "", , acNormal
Forms!frmOrder![ID] = strPushID
ElseIf (Forms!FrmIssues!Combo32 = "Service Call") Then
DoCmd.OpenForm "FrmServiceCall", acNormal, "", "", , acNormal
Forms!frmServiceCall![ID] = strPushID
ElseIf (Forms!FrmIssues!Combo32 = "Consumables") Then
DoCmd.OpenForm "FrmConsumables", acNormal, "", "", , acNormal
Forms!frmConsumables![ID] = strPushID
End If

End Sub

 

[John W. Vinson]

I am using Microsoft Access 2002 SP3. I have one main table named Issues
and several supporting tables all related on the ID field. The Issues table
ID field has an Autonumber to populate the field when a new record is
created. What I would like to accomplish is to have the supporting table’s
auto fill the ID field using the last ID created in the Issues table.

It's almost NEVER either necessary or appropriate to create an empty
"placeholder" record in a related table when a main table record is created.

If you use Subforms on your main form, with the ID as the master/child link
field, the ID will fill in on the subform when (but not before) there is data
entered into the subform. You really *don't* need a record there until there's
something to put into the record!

John W. Vinson [MVP]

 

[Bob M]

John,

I thank you for your insight into my issue. I must then ask what you would
think is the best way to create the many subforms that will be linked to the
main table without using a tab control which will be very confusing to the
user. Is there any way to still have my subforms popup by the selection made
in my combo box? The end result I am looking for is to my main table remain
the center of this project. There will be at least eight or more sub tables
with forms for specific requests. Please excuse my lack of knowledge in this
area as it has been many years since my last major project. Any an all
assistance is greatly appreciated.

Many Thank Again,
Bob

 

[John W. Vinson]

John,

I thank you for your insight into my issue. I must then ask what you would
think is the best way to create the many subforms that will be linked to the
main table without using a tab control which will be very confusing to the
user. Is there any way to still have my subforms popup by the selection made
in my combo box? The end result I am looking for is to my main table remain
the center of this project. There will be at least eight or more sub tables
with forms for specific requests. Please excuse my lack of knowledge in this
area as it has been many years since my last major project. Any an all
assistance is greatly appreciated.

A properly designed tab control will be simpler, not more confusing for the
user! You can use your choice of tabs or buttons to make each page visible;
you can even have a "tabless" tab control and use the AfterUpdate event of the
combo box to choose which tab should be visible.

Or, you could use *one* subform; in the AfterUpdate event of the combo box,
you can set the Source Object property of the subform to the desired form's
name.

Finally, you can indeed use popup forms, if you feel that would be simpler for
the user. It will take a bit of code to keep them synchronized; you'll need to
use the combo box's value in the WhereCondition argument of the OpenForm
method call, and also pass that value in the OpenArgs argument. In the
"subform" you can then set the DefaultValue of the linking field to the value
passed in OpenArgs. It makes for a fair bit of code.

John W. Vinson [MVP]

 

[Bob M]

John,

I am not very familiar the OpenArgs coding if at all possible is there a
generic example of the syntax that I could use for this. I do realize that I
may asking for alot to get into this approach to my problem but I think it
will benefit me greatly to pursue it. On the other end of this if you think
it may be more than I can currently deal with then I would have also ask for
your assitance for the make visible function with the tab controls. I do
appreciate the time constaints that you may have with answering so many
questions and will understand if you may have me make another attempt at fine
tuning my question. As always your time and considration are greatly
appreciated!

Regards,

Bob

 

[John W. Vinson]

John,

I am not very familiar the OpenArgs coding if at all possible is there a
generic example of the syntax that I could use for this.

Well, the answer has to be very generic of course since I don't know any of
your field or form or control names, but it might be something like this. Have
a combo box named cboPopupForm whose bound column contains the name of the
"subform" that you want to open - this should probably be set invisible by
setting the column width to 0 and displaying a user-meaningful value, e.g.

"frmAddress"; "Enter client address"

Private Sub cboPopupForm_AfterUpdate()
Dim strWhere As String
' create a WHERE clause to limit the popup form to the records
' specified in the ID field in the control named txtID
strWhere = "[ID] = " & Me!txtID
DoCmd.OpenForm Me!cboPopupForm, WhereCondition := strWhere, _
OpenArgs := Me!txtID
End Sub

Then in each popup form's Open event:

Private Sub Form_Open(Cancel as Integer)
If Me.OpenArgs & "" <> "" Then ' Did the user pass an OpenArgs?
Me!txtID.DefaultValue = Me.OpenArgs
End If
End Sub

This code should be fleshed out with error trapping, etc. but I'll leave that
as an exercise!

John W. Vinson [MVP]

 

[Bob M]

John,

Thank you for your time and assistance. I think will get me where I need to
go and also give me things to think about as this project continues on. I
will post again if I have another issue.

Many Thanks,
Bob

John,

I am not very familiar the OpenArgs coding if at all possible is there a
generic example of the syntax that I could use for this.

Well, the answer has to be very generic of course since I don't know any of
your field or form or control names, but it might be something like this. Have
a combo box named cboPopupForm whose bound column contains the name of the
"subform" that you want to open - this should probably be set invisible by
setting the column width to 0 and displaying a user-meaningful value, e.g.

"frmAddress"; "Enter client address"

Private Sub cboPopupForm_AfterUpdate()
Dim strWhere As String
' create a WHERE clause to limit the popup form to the records
' specified in the ID field in the control named txtID
strWhere = "[ID] = " & Me!txtID
DoCmd.OpenForm Me!cboPopupForm, WhereCondition := strWhere, _
OpenArgs := Me!txtID
End Sub

Then in each popup form's Open event:

Private Sub Form_Open(Cancel as Integer)
If Me.OpenArgs & "" <> "" Then ' Did the user pass an OpenArgs?
Me!txtID.DefaultValue = Me.OpenArgs
End If
End Sub

This code should be fleshed out with error trapping, etc. but I'll leave that
as an exercise!

John W. Vinson [MVP]