Load form with specific record

[sara]

Hi -
I have been struggling with solution ideas for this now for almost 2
weeks, and have not been able to figure this out.

I have a user who creates a Purchase Order (tblPOData). In some
circumstances, this or another user must create an invoice to go with
the PO (I know - that makes no sense, but this is the business
case).

I have the user go to a form to create the invoice:
frmCreateSpInvoice.

The user chooses the PO from a drop down. Then, the user chooses the
vendor location from another drop down.

Fields from each drop-down populate information fields on the form so
the user can see that they are working with the correct PO. (Fields
like Dollar amount on the PO [from PO data table], City and State of
the Vendor, Vendor Contact [from Vendor table]).

Then the user enters the necessary information and prints an invoice.

The frmCreateSpInvoice is based on a table that just holds invoice
data (tblSpInvoiceData) for this vendor (POKey, VendorLocationKey,
PickUpDate, etc).

I am trying to:

1. If the Invoice record - POKey is unique - does not exist, allow
the user to choose a vendor location, enter date - creates a new
record on tblSPInvoiceData. No problem with this part.

2. If the Invoice record already exists (means the user doesn't know
that someone else created the invoice already, or that they need to
change something like Vendor Location), bring up the existing record.

The problem I'm having is with #2 - I can't figure out how to get the
existing information to show on the form. I understand open in Edit
mode, but since many fields are translations from keys, I can't figure
out how to get the data on the form to edit. I figure there's
something there with a query, but I don't know what that would be.

Happy to post any code - just don't know what's helpful.

Thank you -
Sara

[ruralguy via AccessMonster.com]

You can apply a filter to the next form with the WhereCondition of the
OpenForm command.

sara wrote:

>Hi -
I have been struggling with solution ideas for this now for almost 2
weeks, and have not been able to figure this out.

I have a user who creates a Purchase Order (tblPOData). In some
circumstances, this or another user must create an invoice to go with
the PO (I know - that makes no sense, but this is the business
case).

I have the user go to a form to create the invoice:
frmCreateSpInvoice.

The user chooses the PO from a drop down. Then, the user chooses the
vendor location from another drop down.

Fields from each drop-down populate information fields on the form so
the user can see that they are working with the correct PO. (Fields
like Dollar amount on the PO [from PO data table], City and State of
the Vendor, Vendor Contact [from Vendor table]).

Then the user enters the necessary information and prints an invoice.

The frmCreateSpInvoice is based on a table that just holds invoice
data (tblSpInvoiceData) for this vendor (POKey, VendorLocationKey,
PickUpDate, etc).

I am trying to:

1. If the Invoice record - POKey is unique - does not exist, allow
the user to choose a vendor location, enter date - creates a new
record on tblSPInvoiceData. No problem with this part.

2. If the Invoice record already exists (means the user doesn't know
that someone else created the invoice already, or that they need to
change something like Vendor Location), bring up the existing record.

The problem I'm having is with #2 - I can't figure out how to get the
existing information to show on the form. I understand open in Edit
mode, but since many fields are translations from keys, I can't figure
out how to get the data on the form to edit. I figure there's
something there with a query, but I don't know what that would be.

Happy to post any code - just don't know what's helpful.

Thank you -
Sara

--
HTH - RuralGuy (RG for short) acXP WinXP Pro
Please post back to this forum so all may benefit.

Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...ccess/200705/1

[sara]

On May 9, 4:39 pm, "ruralguy via AccessMonster.com" <u12102@uwe>
wrote:

You can apply a filter to the next form with the WhereCondition of the
OpenForm command.

sara wrote:

Hi -
I have been struggling with solution ideas for this now for almost 2
weeks, and have not been able to figure this out.

I have a user who creates a Purchase Order (tblPOData). In some
circumstances, this or another user must create an invoice to go with
the PO (I know - that makes no sense, but this is the business
case).

I have the user go to a form to create the invoice:
frmCreateSpInvoice.

The user chooses the PO from a drop down. Then, the user chooses the
vendor location from another drop down.

Fields from each drop-down populate information fields on the form so
the user can see that they are working with the correct PO. (Fields
like Dollar amount on the PO [from PO data table], City and State of
the Vendor, Vendor Contact [from Vendor table]).

Then the user enters the necessary information and prints an invoice.

The frmCreateSpInvoice is based on a table that just holds invoice
data (tblSpInvoiceData) for this vendor (POKey, VendorLocationKey,
PickUpDate, etc).

I am trying to:

1. If the Invoice record - POKey is unique - does not exist, allow
the user to choose a vendor location, enter date - creates a new
record on tblSPInvoiceData. No problem with this part.

2. If the Invoice record already exists (means the user doesn't know
that someone else created the invoice already, or that they need to
change something like Vendor Location), bring up the existing record.

The problem I'm having is with #2 - I can't figure out how to get the
existing information to show on the form. I understand open in Edit
mode, but since many fields are translations from keys, I can't figure
out how to get the data on the form to edit. I figure there's
something there with a query, but I don't know what that would be.

Happy to post any code - just don't know what's helpful.

Thank you -
Sara

--
HTH - RuralGuy (RG for short) acXP WinXP Pro
Please post back to this forum so all may benefit.

Message posted via AccessMonster.comhttp://www.accessmonster.com/Uwe/Forums.aspx/databases-ms-access/2007...- Hide quoted text -

- Show quoted text -

I tried this on the open form, and it doesn't work. I can't figure out
how to get certain information. For example: the vendor location is
in a combo box. There are about 5 other fields that are populated
from that combo box in the After Update event. I can't figure out how
to get the VendorLocationKey into the combo box and get the
"Translations" onto the form. The LocationKey goes to a lookup table
to tell me Location City, State, Phone Number, Contact info, Special
Instructions. These all show on the form for the user.

The Where clause didn't work for me:

' Invoice exists; open the form with the data filled in for updating

strFormName = "frmSPInvoice"
strLinkCriteria = "POKey = " & lngPOKey
DoCmd.Close acForm, strFormName

DoCmd.OpenForm strFormName, acNormal, , strLinkCriteria,
acFormEdit
' Load the Key, Number and Cost on the form
Forms!frmSPInvoice.txtPOKey = lngPOKey
Forms!frmSPInvoice.txtPONum = lngPONum
Forms!frmSPInvoice.txtOurCost = curOurCost

I need the other fields from the special invoice table on the form
when it opens so the user can either edit, or see that the invoice is
all ready to print.

Thanks

[ruralguy via AccessMonster.com]

It sounds like there is a lot of work to do on the next form. You will
probably need to pass whatever data you need in the OpenArgs argument of the
OpenForm command and catch them in the Load event of the next form and put
them where they need to be.

sara wrote:

>On May 9, 4:39 pm, "ruralguy via AccessMonster.com" <u12102@uwe>
wrote:

>You can apply a filter to the next form with the WhereCondition of the
OpenForm command.

[quoted text clipped - 53 lines]

>>
- Show quoted text -

I tried this on the open form, and it doesn't work. I can't figure out
how to get certain information. For example: the vendor location is
in a combo box. There are about 5 other fields that are populated
from that combo box in the After Update event. I can't figure out how
to get the VendorLocationKey into the combo box and get the
"Translations" onto the form. The LocationKey goes to a lookup table
to tell me Location City, State, Phone Number, Contact info, Special
Instructions. These all show on the form for the user.

The Where clause didn't work for me:

' Invoice exists; open the form with the data filled in for updating

strFormName = "frmSPInvoice"
strLinkCriteria = "POKey = " & lngPOKey
DoCmd.Close acForm, strFormName

DoCmd.OpenForm strFormName, acNormal, , strLinkCriteria,
acFormEdit
' Load the Key, Number and Cost on the form
Forms!frmSPInvoice.txtPOKey = lngPOKey
Forms!frmSPInvoice.txtPONum = lngPONum
Forms!frmSPInvoice.txtOurCost = curOurCost

I need the other fields from the special invoice table on the form
when it opens so the user can either edit, or see that the invoice is
all ready to print.

Thanks

--
HTH - RuralGuy (RG for short) acXP WinXP Pro
Please post back to this forum so all may benefit.

Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...ccess/200705/1

[sara]

On May 9, 5:49 pm, "ruralguy via AccessMonster.com" <u12102@uwe>
wrote:

It sounds like there is a lot of work to do on the next form. You will
probably need to pass whatever data you need in the OpenArgs argument of the
OpenForm command and catch them in the Load event of the next form and put
them where they need to be.

sara wrote:

On May 9, 4:39 pm, "ruralguy via AccessMonster.com" <u12102@uwe>
wrote:

You can apply a filter to the next form with the WhereCondition of the
OpenForm command.

[quoted text clipped - 53 lines]

- Show quoted text -

I tried this on the open form, and it doesn't work. I can't figure out
how to get certain information. For example: the vendor location is
in a combo box. There are about 5 other fields that are populated
from that combo box in the After Update event. I can't figure out how
to get the VendorLocationKey into the combo box and get the
"Translations" onto the form. The LocationKey goes to a lookup table
to tell me Location City, State, Phone Number, Contact info, Special
Instructions. These all show on the form for the user.

The Where clause didn't work for me:

' Invoice exists; open the form with the data filled in for updating

strFormName = "frmSPInvoice"
strLinkCriteria = "POKey = " & lngPOKey
DoCmd.Close acForm, strFormName

DoCmd.OpenForm strFormName, acNormal, , strLinkCriteria,
acFormEdit
' Load the Key, Number and Cost on the form
Forms!frmSPInvoice.txtPOKey = lngPOKey
Forms!frmSPInvoice.txtPONum = lngPONum
Forms!frmSPInvoice.txtOurCost = curOurCost

I need the other fields from the special invoice table on the form
when it opens so the user can either edit, or see that the invoice is
all ready to print.

Thanks

--
HTH - RuralGuy (RG for short) acXP WinXP Pro
Please post back to this forum so all may benefit.

Message posted via AccessMonster.comhttp://www.accessmonster.com/Uwe/Forums.aspx/databases-ms-access/2007...- Hide quoted text -

- Show quoted text -

I haven't ever used OpenArgs, and have read Help on them. Is there
any code saple somewhere I could see to follow as an example? I am
interested in the Open Args and then how to "catch them in the load
event of the next form and put them where they should be".

Thanks.
Sara

[ruralguy via AccessMonster.com]

The OpenArgs argument is simply a string with the parameters separated with a
common character.
I've used a semicolon in this case to separate the parameters. This allows us
to use the Split function on the other end to break the parameters apart.

strFormName = "frmSPInvoice"
strOpenArgs = lngPOKey & ";"
strOpenArgs = strOpenArgs & lngPONum & ";"
strOpenArgs = strOpenArgs & curOurCost

DoCmd.OpenForm strFormName, , , , acFormEdit, acDialog, strOpenArgs
I used the acDialog WindowMode to halt code until the frmSPInvoice form is
closed.

Catching the arguments in the load event of the next form is simply:

Private Sub Form_Load()
Dim Args As Variant
If Not IsNull(Me.OpenArgs) Then
'-- Form is being opened from a form passing parameters
Args = Split(Me.OpenArgs, ";")
Me.txtPOKey = Args(0) '-- 1st parameters
Me.txtPONum = Args(1) '-- 2nd parameters
Me.txtOurCost = Args(2) '-- 3rd parameters
End If
End Sub

sara wrote:

>On May 9, 5:49 pm, "ruralguy via AccessMonster.com" <u12102@uwe>
wrote:

>It sounds like there is a lot of work to do on the next form. You will
probably need to pass whatever data you need in the OpenArgs argument of the

[quoted text clipped - 46 lines]

>>
- Show quoted text -

I haven't ever used OpenArgs, and have read Help on them. Is there
any code saple somewhere I could see to follow as an example? I am
interested in the Open Args and then how to "catch them in the load
event of the next form and put them where they should be".

Thanks.
Sara

--
HTH - RuralGuy (RG for short) acXP WinXP Pro
Please post back to this forum so all may benefit.

Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...ccess/200705/1

[sara]

On May 10, 8:49 am, "ruralguy via AccessMonster.com" <u12102@uwe>
wrote:

The OpenArgs argument is simply a string with the parameters separated with a
common character.
I've used a semicolon in this case to separate the parameters. This allows us
to use the Split function on the other end to break the parameters apart.

strFormName = "frmSPInvoice"
strOpenArgs = lngPOKey & ";"
strOpenArgs = strOpenArgs & lngPONum & ";"
strOpenArgs = strOpenArgs & curOurCost

DoCmd.OpenForm strFormName, , , , acFormEdit, acDialog, strOpenArgs
I used the acDialog WindowMode to halt code until the frmSPInvoice form is
closed.

Catching the arguments in the load event of the next form is simply:

Private Sub Form_Load()
Dim Args As Variant
If Not IsNull(Me.OpenArgs) Then
'-- Form is being opened from a form passing parameters
Args = Split(Me.OpenArgs, ";")
Me.txtPOKey = Args(0) '-- 1st parameters
Me.txtPONum = Args(1) '-- 2nd parameters
Me.txtOurCost = Args(2) '-- 3rd parameters
End If
End Sub

sara wrote:

On May 9, 5:49 pm, "ruralguy via AccessMonster.com" <u12102@uwe>
wrote:

It sounds like there is a lot of work to do on the next form. You will
probably need to pass whatever data you need in the OpenArgs argument of the

[quoted text clipped - 46 lines]

- Show quoted text -

I haven't ever used OpenArgs, and have read Help on them. Is there
any code saple somewhere I could see to follow as an example? I am
interested in the Open Args and then how to "catch them in the load
event of the next form and put them where they should be".

Thanks.
Sara

--
HTH - RuralGuy (RG for short) acXP WinXP Pro
Please post back to this forum so all may benefit.

Message posted via AccessMonster.comhttp://www.accessmonster.com/Uwe/Forums.aspx/databases-ms-access/2007...- Hide quoted text -

- Show quoted text -

Thanks. I'm going to work on this right now - Strike while the iron
is hot (and the brain is engaged)

Will let you know how it goes.

sara

[sara]

On May 10, 8:49 am, "ruralguy via AccessMonster.com" <u12102@uwe>
wrote:

The OpenArgs argument is simply a string with the parameters separated with a
common character.
I've used a semicolon in this case to separate the parameters. This allows us
to use the Split function on the other end to break the parameters apart.

strFormName = "frmSPInvoice"
strOpenArgs = lngPOKey & ";"
strOpenArgs = strOpenArgs & lngPONum & ";"
strOpenArgs = strOpenArgs & curOurCost

DoCmd.OpenForm strFormName, , , , acFormEdit, acDialog, strOpenArgs
I used the acDialog WindowMode to halt code until the frmSPInvoice form is
closed.

Catching the arguments in the load event of the next form is simply:

Private Sub Form_Load()
Dim Args As Variant
If Not IsNull(Me.OpenArgs) Then
'-- Form is being opened from a form passing parameters
Args = Split(Me.OpenArgs, ";")
Me.txtPOKey = Args(0) '-- 1st parameters
Me.txtPONum = Args(1) '-- 2nd parameters
Me.txtOurCost = Args(2) '-- 3rd parameters
End If
End Sub

sara wrote:

On May 9, 5:49 pm, "ruralguy via AccessMonster.com" <u12102@uwe>
wrote:

It sounds like there is a lot of work to do on the next form. You will
probably need to pass whatever data you need in the OpenArgs argument of the

[quoted text clipped - 46 lines]

- Show quoted text -

I haven't ever used OpenArgs, and have read Help on them. Is there
any code saple somewhere I could see to follow as an example? I am
interested in the Open Args and then how to "catch them in the load
event of the next form and put them where they should be".

Thanks.
Sara

--
HTH - RuralGuy (RG for short) acXP WinXP Pro
Please post back to this forum so all may benefit.

Message posted via AccessMonster.comhttp://www.accessmonster.com/Uwe/Forums.aspx/databases-ms-access/2007...- Hide quoted text -

- Show quoted text -

I'm closer - now have a few new problems. I'm posting code here as
well.

1. I took out the acDialog - didn't really undersand it. My thinking
was to "prevent" code from "zipping along" without completing the
prior steps. But then the form opened funny and I couldn't use it
normally. Seems to work without acDialog - please advise if I've
missed something critical here and am doing a stupid thing I'll pay
for later.

2. I can't get the form to recognize it's got an existing record. I
can't save any changes - and I have to load every field on the form or
I get the first record in the table. I hide the key to the table when
I'm on the form, and when I look at its value, I see "5" - the autonum
key field of the first record in the table.

Don't know how to get the form to know it's an existing record and to
update when the user presses "Save", not try to add a new record.

But I think I've learned OpenArgs - pretty neat! And Split is great,
too. Thanks.

Thanks -
Sara

Code that runs after the user Chooses a PO

Private Sub cboSPPONum_AfterUpdate()
On Error GoTo Err_cboSPPONum_AfterUpdate

' After the PO is chosen, determine if the record exists:
' open the form with that data in edit mode

' Or if it's a new record:
' load the fields and allow the user to create a new record

Dim lngPOKey As Long
Dim lngPONum As Long
Dim curOurCost As Currency
Dim intCount As Integer
Dim strLinkCriteria As String
Dim strFormName As String

' For existing Record, need the info
Dim strSPLocation As String
Dim strSPPhoneNumber As String
Dim strSPContact As String
Dim strSPInfo As String
Dim strSPExtension As String

Dim strOpenArgs As String
Dim lngSPLocationKey As Long
Dim strComments As String
lngPOKey = Nz(Me.cboSPPONum.Column(0), 0)
lngPONum = Nz(Me.cboSPPONum.Column(1), 0)
curOurCost = Nz(Me.cboSPPONum.Column(4), 0)

intCount = DCount("SPDataKey", "tblSPData", "POKey = " & lngPOKey)
If intCount = 0 Then
' New invoice data - form is already open in Add mode
Me.txtPOKey = lngPOKey
Me.txtPONum = lngPONum
Me.txtOurCost = curOurCost
GoTo Exit_cboSPPONum_AfterUpdate
End If
' Invoice exists; open the form with the data filled in for updating

strFormName = "frmSPInvoice"

lngSPLocationKey = DLookup("SPLocationKey", "tblSPData", "POKey =
" & lngPOKey)
strComments = DLookup("Comments", "tblSPData", "POKey = " &
lngPOKey)

strOpenArgs = lngPOKey & ";"
strOpenArgs = strOpenArgs & lngPONum & ";"
strOpenArgs = strOpenArgs & curOurCost & ";"
strOpenArgs = strOpenArgs & lngSPLocationKey & ";"
strOpenArgs = strOpenArgs & strComments

DoCmd.Close acForm, strFormName

DoCmd.OpenForm strFormName, , , , acFormEdit, , strOpenArgs
' Loads the info on the form - from Load Event


Exit_cboSPPONum_AfterUpdate:
Exit Sub

Err_cboSPPONum_AfterUpdate:
MsgBox Err.Number & " " & Err.Description, , "PO - Error SP
Invoice"
Resume Exit_cboSPPONum_AfterUpdate

End Sub

Form's Load Event Code:
Private Sub Form_Load()
On Error GoTo Err_Form_Load

Dim Args As Variant
If Not IsNull(Me.OpenArgs) Then
' Form is being opened from a form which is passing parameters
Args = Split(Me.OpenArgs, ";")
Me.txtPOKey = Args(0)
Me.txtPONum = Args(1)
Me.txtOurCost = Args(2)
Me.cboSPLocation = Args(3)
Me.cboSPLocation.Requery
Me.txtComments = Args(4)

Me.txtSPLocation = Me.cboSPLocation.Column(1) & ", " &
Me.cboSPLocation.Column(2)
Me.txtSPPhoneNumber = Me.cboSPLocation.Column(5)
Me.txtSPContact = Me.cboSPLocation.Column(4)
Me.txtSPInfo = Me.cboSPLocation.Column(7)
Me.txtSPExtension = Me.cboSPLocation.Column(6)
Me.txtSPLocationKey = Me.cboSPLocation.Column(0)

End If

' When the form opens, show the existing record if one exists, else
nothing
Exit_Form_Load:
Exit Sub

Err_Form_Load:
MsgBox Err.Number & " " & Err.Description, , "PO - SP Invoice Form
Load error"
Resume Exit_Form_Load

End Sub

[ruralguy via AccessMonster.com]

I forgot you were closing the calling form. In that case, acDialog makes no
sense so you were correct in deleting it. It is not necessary to do DLookup()
in the first form and then pass the answer to the next form as a parameter in
OpenArgs. Just pass the KEY and let the next form do the Dlookup()'s. You
will also need to do a FindFirst with the KEY to move the recordset to the
record you want. Post back if you want further instructions.

sara wrote:

>On May 10, 8:49 am, "ruralguy via AccessMonster.com" <u12102@uwe>
wrote:

>The OpenArgs argument is simply a string with the parameters separated with a
common character.

[quoted text clipped - 46 lines]

>>
- Show quoted text -

I'm closer - now have a few new problems. I'm posting code here as
well.

1. I took out the acDialog - didn't really undersand it. My thinking
was to "prevent" code from "zipping along" without completing the
prior steps. But then the form opened funny and I couldn't use it
normally. Seems to work without acDialog - please advise if I've
missed something critical here and am doing a stupid thing I'll pay
for later.

2. I can't get the form to recognize it's got an existing record. I
can't save any changes - and I have to load every field on the form or
I get the first record in the table. I hide the key to the table when
I'm on the form, and when I look at its value, I see "5" - the autonum
key field of the first record in the table.

Don't know how to get the form to know it's an existing record and to
update when the user presses "Save", not try to add a new record.

But I think I've learned OpenArgs - pretty neat! And Split is great,
too. Thanks.

Thanks -
Sara

Code that runs after the user Chooses a PO

Private Sub cboSPPONum_AfterUpdate()
On Error GoTo Err_cboSPPONum_AfterUpdate

' After the PO is chosen, determine if the record exists:
' open the form with that data in edit mode

' Or if it's a new record:
' load the fields and allow the user to create a new record

Dim lngPOKey As Long
Dim lngPONum As Long
Dim curOurCost As Currency
Dim intCount As Integer
Dim strLinkCriteria As String
Dim strFormName As String

' For existing Record, need the info
Dim strSPLocation As String
Dim strSPPhoneNumber As String
Dim strSPContact As String
Dim strSPInfo As String
Dim strSPExtension As String

Dim strOpenArgs As String
Dim lngSPLocationKey As Long
Dim strComments As String

lngPOKey = Nz(Me.cboSPPONum.Column(0), 0)
lngPONum = Nz(Me.cboSPPONum.Column(1), 0)
curOurCost = Nz(Me.cboSPPONum.Column(4), 0)

intCount = DCount("SPDataKey", "tblSPData", "POKey = " & lngPOKey)
If intCount = 0 Then
' New invoice data - form is already open in Add mode
Me.txtPOKey = lngPOKey
Me.txtPONum = lngPONum
Me.txtOurCost = curOurCost
GoTo Exit_cboSPPONum_AfterUpdate
End If

' Invoice exists; open the form with the data filled in for updating

strFormName = "frmSPInvoice"

lngSPLocationKey = DLookup("SPLocationKey", "tblSPData", "POKey =
" & lngPOKey)
strComments = DLookup("Comments", "tblSPData", "POKey = " &
lngPOKey)

strOpenArgs = lngPOKey & ";"
strOpenArgs = strOpenArgs & lngPONum & ";"
strOpenArgs = strOpenArgs & curOurCost & ";"
strOpenArgs = strOpenArgs & lngSPLocationKey & ";"
strOpenArgs = strOpenArgs & strComments

DoCmd.Close acForm, strFormName

DoCmd.OpenForm strFormName, , , , acFormEdit, , strOpenArgs
' Loads the info on the form - from Load Event

Exit_cboSPPONum_AfterUpdate:
Exit Sub

Err_cboSPPONum_AfterUpdate:
MsgBox Err.Number & " " & Err.Description, , "PO - Error SP
Invoice"
Resume Exit_cboSPPONum_AfterUpdate

End Sub

Form's Load Event Code:

Private Sub Form_Load()
On Error GoTo Err_Form_Load

Dim Args As Variant

If Not IsNull(Me.OpenArgs) Then
' Form is being opened from a form which is passing parameters
Args = Split(Me.OpenArgs, ";")
Me.txtPOKey = Args(0)
Me.txtPONum = Args(1)
Me.txtOurCost = Args(2)
Me.cboSPLocation = Args(3)
Me.cboSPLocation.Requery
Me.txtComments = Args(4)

Me.txtSPLocation = Me.cboSPLocation.Column(1) & ", " &
Me.cboSPLocation.Column(2)
Me.txtSPPhoneNumber = Me.cboSPLocation.Column(5)
Me.txtSPContact = Me.cboSPLocation.Column(4)
Me.txtSPInfo = Me.cboSPLocation.Column(7)
Me.txtSPExtension = Me.cboSPLocation.Column(6)
Me.txtSPLocationKey = Me.cboSPLocation.Column(0)

End If

' When the form opens, show the existing record if one exists, else
nothing

Exit_Form_Load:
Exit Sub

Err_Form_Load:
MsgBox Err.Number & " " & Err.Description, , "PO - SP Invoice Form
Load error"
Resume Exit_Form_Load

End Sub

--
HTH - RuralGuy (RG for short) acXP WinXP Pro
Please post back to this forum so all may benefit.

Message posted via http://www.accessmonster.com

[sara]

On May 10, 2:47 pm, "ruralguy via AccessMonster.com" <u12102@uwe>
wrote:

I forgot you were closing the calling form. In that case, acDialog makes no
sense so you were correct in deleting it. It is not necessary to do DLookup()
in the first form and then pass the answer to the next form as a parameter in
OpenArgs. Just pass the KEY and let the next form do the Dlookup()'s. You
will also need to do a FindFirst with the KEY to move the recordset to the
record you want. Post back if you want further instructions.

sara wrote:

On May 10, 8:49 am, "ruralguy via AccessMonster.com" <u12102@uwe>
wrote:

The OpenArgs argument is simply a string with the parameters separated with a
common character.

[quoted text clipped - 46 lines]

- Show quoted text -

I'm closer - now have a few new problems. I'm posting code here as
well.

1. I took out the acDialog - didn't really undersand it. My thinking
was to "prevent" code from "zipping along" without completing the
prior steps. But then the form opened funny and I couldn't use it
normally. Seems to work without acDialog - please advise if I've
missed something critical here and am doing a stupid thing I'll pay
for later.

2. I can't get the form to recognize it's got an existing record. I
can't save any changes - and I have to load every field on the form or
I get the first record in the table. I hide the key to the table when
I'm on the form, and when I look at its value, I see "5" - the autonum
key field of the first record in the table.

Don't know how to get the form to know it's an existing record and to
update when the user presses "Save", not try to add a new record.

But I think I've learned OpenArgs - pretty neat! And Split is great,
too. Thanks.

Thanks -
Sara

Code that runs after the user Chooses a PO

Private Sub cboSPPONum_AfterUpdate()
On Error GoTo Err_cboSPPONum_AfterUpdate

' After the PO is chosen, determine if the record exists:
' open the form with that data in edit mode

' Or if it's a new record:
' load the fields and allow the user to create a new record

Dim lngPOKey As Long
Dim lngPONum As Long
Dim curOurCost As Currency
Dim intCount As Integer
Dim strLinkCriteria As String
Dim strFormName As String

' For existing Record, need the info
Dim strSPLocation As String
Dim strSPPhoneNumber As String
Dim strSPContact As String
Dim strSPInfo As String
Dim strSPExtension As String

Dim strOpenArgs As String
Dim lngSPLocationKey As Long
Dim strComments As String

lngPOKey = Nz(Me.cboSPPONum.Column(0), 0)
lngPONum = Nz(Me.cboSPPONum.Column(1), 0)
curOurCost = Nz(Me.cboSPPONum.Column(4), 0)

intCount = DCount("SPDataKey", "tblSPData", "POKey = " & lngPOKey)
If intCount = 0 Then
' New invoice data - form is already open in Add mode
Me.txtPOKey = lngPOKey
Me.txtPONum = lngPONum
Me.txtOurCost = curOurCost
GoTo Exit_cboSPPONum_AfterUpdate
End If

' Invoice exists; open the form with the data filled in for updating

strFormName = "frmSPInvoice"

lngSPLocationKey = DLookup("SPLocationKey", "tblSPData", "POKey =
" & lngPOKey)
strComments = DLookup("Comments", "tblSPData", "POKey = " &
lngPOKey)

strOpenArgs = lngPOKey & ";"
strOpenArgs = strOpenArgs & lngPONum & ";"
strOpenArgs = strOpenArgs & curOurCost & ";"
strOpenArgs = strOpenArgs & lngSPLocationKey & ";"
strOpenArgs = strOpenArgs & strComments

DoCmd.Close acForm, strFormName

DoCmd.OpenForm strFormName, , , , acFormEdit, , strOpenArgs
' Loads the info on the form - from Load Event

Exit_cboSPPONum_AfterUpdate:
Exit Sub

Err_cboSPPONum_AfterUpdate:
MsgBox Err.Number & " " & Err.Description, , "PO - Error SP
Invoice"
Resume Exit_cboSPPONum_AfterUpdate

End Sub

Form's Load Event Code:

Private Sub Form_Load()
On Error GoTo Err_Form_Load

Dim Args As Variant

If Not IsNull(Me.OpenArgs) Then
' Form is being opened from a form which is passing parameters
Args = Split(Me.OpenArgs, ";")
Me.txtPOKey = Args(0)
Me.txtPONum = Args(1)
Me.txtOurCost = Args(2)
Me.cboSPLocation = Args(3)
Me.cboSPLocation.Requery
Me.txtComments = Args(4)

Me.txtSPLocation = Me.cboSPLocation.Column(1) & ", " &
Me.cboSPLocation.Column(2)
Me.txtSPPhoneNumber = Me.cboSPLocation.Column(5)
Me.txtSPContact = Me.cboSPLocation.Column(4)
Me.txtSPInfo = Me.cboSPLocation.Column(7)
Me.txtSPExtension = Me.cboSPLocation.Column(6)
Me.txtSPLocationKey = Me.cboSPLocation.Column(0)

End If

' When the form opens, show the existing record if one exists, else
nothing

Exit_Form_Load:
Exit Sub

Err_Form_Load:
MsgBox Err.Number & " " & Err.Description, , "PO - SP Invoice Form
Load error"
Resume Exit_Form_Load

End Sub

--
HTH - RuralGuy (RG for short) acXP WinXP Pro
Please post back to this forum so all may benefit.

Message posted viahttp://www.accessmonster.com- Hide quoted text -

- Show quoted text -

Hi -
I tried the "key" only - still didn't work.

The thing is, I'm not opening a second form. I'm re-opening the first
form. Let me (try to) explain:

I have a form with 2 combo boxes at the top. The first is for the PO
the user is working on. Once the user choose a PO, 2 display-only
fields are populated on the form. (via the After-Update event). THe
POKey is stored on the table to which the form is bound.

The second box allows the user to choose the vendor location. Once
chosen, 5 more diplay fields are filled in on the form. These are
information about the vendor, found on the vendor lookup table. (After-
Update event again). The VendorLocationKey is stored on the table to
which the form is bound.

Then there are 3 fields the user enters on the form. All 3 are
written to the table to which the form is bound.

What I want is when the user chooses a PO to either have the form
allow a new recored (for that PO) or show the values in the record for
update (for that PO). All on one form.

Make sense? I could email the db to you - don't know how to attach
something here.

Sara

[ruralguy via AccessMonster.com]

I don't think this forum accepts binaries. Go ahead and send your zipped up
db to Rural Guy at Wild Blue dot Net. Do a Tools>Database Utilities>Compact
and Repair before you zip it.

sara wrote:

>On May 10, 2:47 pm, "ruralguy via AccessMonster.com" <u12102@uwe>
wrote:

>I forgot you were closing the calling form. In that case, acDialog makes no
sense so you were correct in deleting it. It is not necessary to do DLookup()

[quoted text clipped - 155 lines]

>>
- Show quoted text -

Hi -
I tried the "key" only - still didn't work.

The thing is, I'm not opening a second form. I'm re-opening the first
form. Let me (try to) explain:

I have a form with 2 combo boxes at the top. The first is for the PO
the user is working on. Once the user choose a PO, 2 display-only
fields are populated on the form. (via the After-Update event). THe
POKey is stored on the table to which the form is bound.

The second box allows the user to choose the vendor location. Once
chosen, 5 more diplay fields are filled in on the form. These are
information about the vendor, found on the vendor lookup table. (After-
Update event again). The VendorLocationKey is stored on the table to
which the form is bound.

Then there are 3 fields the user enters on the form. All 3 are
written to the table to which the form is bound.

What I want is when the user chooses a PO to either have the form
allow a new recored (for that PO) or show the values in the record for
update (for that PO). All on one form.

Make sense? I could email the db to you - don't know how to attach
something here.

Sara

--
HTH - RuralGuy (RG for short) acXP WinXP Pro
Please post back to this forum so all may benefit.

Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...ccess/200705/1