Autofill ID from one table to other

G

Guest

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
 
G

Guest

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
 
G

Guest

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

Maurice said:
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


Bob M said:
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
Click to expand...
Click to expand...
 
G

Guest

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

Bob M said:
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

Maurice said:
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


Bob M said:
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
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Autofill ID fields 6
Function and Syntax problems 4
Open form to specific record from button on continuous form 4
DoCmd.OpenForm problem 2
Code looping back to itself unexpectedly 3
Problem with ReplicationID 1
Accessing data from a subform in the parent form's recordset 15
Difference between Macro and code 1

Top