Autofill ID from one table to other

[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. The supporting tables can have many
records with the same ID.

Any and all assistance is greatly appreciated,
Bob

Below is what I currently have:

Private Sub Combo32_Change()

DoCmd.RunCommand acCmdSaveRecord

If (Forms!FrmIssues!Combo32 = "Ordering") Then
DoCmd.OpenForm "FrmOrder", acNormal, "", "", , acNormal

End If


If (Forms!FrmIssues!Combo32 = "Service Call") Then
DoCmd.OpenForm "FrmServiceCall", acNormal, "", "", , acNormal

End If

If (Forms!FrmIssues!Combo32 = "Consumables") Then
DoCmd.OpenForm "FrmConsumables", acNormal, "", "", , acNormal

End If

End Sub

 

[Maurice]

Bob,

This might cause some troubles in a multi-user enviroment though. But maybe
you could push the value like:

Private Sub Combo32_Change()

DoCmd.RunCommand acCmdSaveRecord
Dim strPushID as integer
strPushID=me.[yourautonumberfield]

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

End Sub

hth

 

[Bob M]

Marice,

This works exactly like I am wanting it to. I am guessing that the troubles
will more than likely come somewhere down the road. If they do you may see
my name again. But for now I thank you for for assistance and am very
greatful of the services you and the others provide. You know dont know how
mant times I have posted this issue and not recieved any resolution. On the
other hand I accept most of that responsibility as my teminology was a little
outdated and rusty.

Many Thanks Again,

Bob

Bob,

This might cause some troubles in a multi-user enviroment though. But maybe
you could push the value like:

Private Sub Combo32_Change()

DoCmd.RunCommand acCmdSaveRecord
Dim strPushID as integer
strPushID=me.[yourautonumberfield]

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

End Sub

hth

--
Maurice Ausum

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. The supporting tables can have many
records with the same ID.

Any and all assistance is greatly appreciated,
Bob

Below is what I currently have:

Private Sub Combo32_Change()

DoCmd.RunCommand acCmdSaveRecord

If (Forms!FrmIssues!Combo32 = "Ordering") Then
DoCmd.OpenForm "FrmOrder", acNormal, "", "", , acNormal

End If


If (Forms!FrmIssues!Combo32 = "Service Call") Then
DoCmd.OpenForm "FrmServiceCall", acNormal, "", "", , acNormal

End If

If (Forms!FrmIssues!Combo32 = "Consumables") Then
DoCmd.OpenForm "FrmConsumables", acNormal, "", "", , acNormal

End If

End Sub

 

[Bob M]

Maurice,

First and foremost I apologize for incorrecly spelling your name. Now for
the good stuff. I had tested this out and at first I thought it was working
the way I needed it to. After further investigation I have found that when
the form opens it does autofill the ID field, but if I attempt to create a
second record using the same ID I would have to fill it in manually. Is
there a way to have more than one record autofill with the same ID if the
main form (Issuesfrm) closes when the selected form opens?

Bob

Marice,

This works exactly like I am wanting it to. I am guessing that the troubles
will more than likely come somewhere down the road. If they do you may see
my name again. But for now I thank you for for assistance and am very
greatful of the services you and the others provide. You know dont know how
mant times I have posted this issue and not recieved any resolution. On the
other hand I accept most of that responsibility as my teminology was a little
outdated and rusty.

Many Thanks Again,

Bob

Bob,

This might cause some troubles in a multi-user enviroment though. But maybe
you could push the value like:

Private Sub Combo32_Change()

DoCmd.RunCommand acCmdSaveRecord
Dim strPushID as integer
strPushID=me.[yourautonumberfield]

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

End Sub

hth

--
Maurice Ausum

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. The supporting tables can have many
records with the same ID.

Any and all assistance is greatly appreciated,
Bob

Below is what I currently have:

Private Sub Combo32_Change()

DoCmd.RunCommand acCmdSaveRecord

If (Forms!FrmIssues!Combo32 = "Ordering") Then
DoCmd.OpenForm "FrmOrder", acNormal, "", "", , acNormal

End If


If (Forms!FrmIssues!Combo32 = "Service Call") Then
DoCmd.OpenForm "FrmServiceCall", acNormal, "", "", , acNormal

End If

If (Forms!FrmIssues!Combo32 = "Consumables") Then
DoCmd.OpenForm "FrmConsumables", acNormal, "", "", , acNormal

End If

End Sub