Passing data onto another form

[hansnyc]

Hi,
i'm quite new to Access and I have a problem passing data from one form onto another.

I have a form that I use for quotations based on the table T Quotations on it I have:
QuotationID as my primary
RefCust referring to which customer
Date
and a few other fields

Now i'd like to add a button that when clicked will open my order form. In other words when a quote is confirmed I'd like to click on a confirmation button on the quotation form to confirm that quote and create an order on the order form.
Order form will open with an orderID autogenerated BUT will return the same RefCust as on the Quotation form, the same date and will give the quotationID as Refquote onto the new form.

Ex: on quotation form I have
Cust: abc company
QuotationID: 123
Date: Feb 25 2007
Quote: $300

when i click on confirm

the Order form will open with:
OrderID: 002 (autogenerated)
RefQuoteID: 123
Date: Feb 25 2007 (even though today might be April 10)
RefQuote: $300

Please help me

 

[MStef]

Hello hansnyc!

1) You don't need the same data in two tables. In "Order" form (Order table),
it's enough "RefOuoteID" as a foreign key. you don't need Date and RefQuote.

2) I suggest you to make "Order" form as a SUBFORM in "Quotations" form.
Link them via "QuotationID" - "RefQuoteID".

 

[hansnyc]

pass data from one form to another

My second form has 2 many info
and the first form already has a subform
I need to pass info from that quoteID on form 1 to refquoteID on form 2
Can you help?
please

1) You don't need the same data in two tables. In "Order" form (Order table),
it's enough "RefOuoteID" as a foreign key. you don't need Date and RefQuote.

2) I suggest you to make "Order" form as a SUBFORM in "Quotations" form.
Link them via "QuotationID" - "RefQuoteID".

 

[RuralGuy]

You can pass a large number of parameters to the next form in the OpenArgs argument. What version of Access are you using?

 

[hansnyc]

Access 2003 V.11

You can pass a large number of parameters to the next form in the OpenArgs argument. What version of Access are you using?

 

[hansnyc]

I do not know how to pass with OpenArgs

Could you give me a code exemple

Form 1 = F Quotations
Form 2 = F Orders

Form 1 fields to pass = QuoteID, RefCust*, Quote
Ex: QuoteID = 123, RefCust = abc company, Quote = $300

Form 2 fields to receive = RefQuoteID, RefCust, RefQuote

*RefCust refers to a table called Customers in F Quotations I pick a customer from a combo box referring to that table

Thanks for your help

You can pass a large number of parameters to the next form in the OpenArgs argument. What version of Access are you using?

 

[RuralGuy]

Post the code line you use to open the 2nd form.
DoCmd.OpenForm....

 

[RuralGuy]

I'm headed to the sack right now. I'll be back on in the morning.

 

[hansnyc]

Here is the code from the cmd button to Open Form 2
I did it with a wizard (dont laugh I'm a newbie)
Thanks for your help

Private Sub ComOpenOrder_Click()
On Error GoTo Err_ComOpenOrder_Click

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "F Orders"
DoCmd.OpenForm stDocName, , , stLinkCriteria

Exit_ComOpenOrder_Click:
Exit Sub

Err_ComOpenOrder_Click:
MsgBox Err.Description
Resume Exit_ComOpenOrder_Click

End Sub

Post the code line you use to open the 2nd form.
DoCmd.OpenForm....

 

[RuralGuy]

Private Sub ComOpenOrder_Click()
On Error GoTo Err_ComOpenOrder_Click

Dim stDocName As String
Dim stLinkCriteria As String
[COLOR="Red"]Dim strArgs As String[/COLOR]

stDocName = "F Orders"
[COLOR="Red"]strArgs = Me.QuoteID & ";" & _
          Me.RefCust & ";" & _
          Me.Quote
DoCmd.OpenForm stDocName, , , , , , strArgs '-- 7th argument[/COLOR]
Exit_ComOpenOrder_Click:
Exit Sub

Err_ComOpenOrder_Click:
MsgBox Err.Description
Resume Exit_ComOpenOrder_Click

End Sub

And in the [F Orders] form OnLoad event put:

Private Sub Form_Load()

Dim Args As Variant

If Not IsNull(Me.OpenArgs) Then
    '-- Form is being opened from a form passing values
    Args = Split(Me.OpenArgs, ";")
    Me.RefQuoteID = Args(0)    '-- Assuming a numeric value
    Me.RefCust = Args(1)       '-- A string 
    Me.RefQuote = Args(2)      '-- Again a numeric value
End If

End Sub

 

[RuralGuy]

BTW you should really eliminate the embeded space in any names. Here's some links to review:
List of reserved words in Access 2002 and Access 2003
List of Microsoft Jet 4.0 reserved words
Special characters that you must avoid when you work with Access databases

 

[hansnyc]

Thank you so much for the codes
The data transfers but goes to the first record
I need the form to open to a new record and put the things in
Do you know how and where to put open new?
Thanks so much for your help
You're a life Saver!!!

Private Sub ComOpenOrder_Click()
On Error GoTo Err_ComOpenOrder_Click

Dim stDocName As String
Dim stLinkCriteria As String
[COLOR="Red"]Dim strArgs As String[/COLOR]

stDocName = "F Orders"
[COLOR="Red"]strArgs = Me.QuoteID & ";" & _
          Me.RefCust & ";" & _
          Me.Quote
DoCmd.OpenForm stDocName, , , , , , strArgs '-- 7th argument[/COLOR]
Exit_ComOpenOrder_Click:
Exit Sub

Err_ComOpenOrder_Click:
MsgBox Err.Description
Resume Exit_ComOpenOrder_Click

End Sub

And in the [F Orders] form OnLoad event put:

Private Sub Form_Load()

Dim Args As Variant

If Not IsNull(Me.OpenArgs) Then
    '-- Form is being opened from a form passing values
    Args = Split(Me.OpenArgs, ";")
    Me.RefQuoteID = Args(0)    '-- Assuming a numeric value
    Me.RefCust = Args(1)       '-- A string 
    Me.RefQuote = Args(2)      '-- Again a numeric value
End If

End Sub

 

[RuralGuy]

It is the 5th argument:
DoCmd.OpenForm stDocName, , , , acFormAdd, , strArgs

 

[hansnyc]

Thank you so much
it worked like a charm
I hope this helps someone else to pass data to another form.

It is the 5th argument:
DoCmd.OpenForm stDocName, , , , acFormAdd, , strArgs

 

[RuralGuy]

Glad I could help.

 

[lution]

Thanks guys, I was trying to figure out how to do this too and this thread helped. I have a citations form. One of the controls is a combo box with a list of municipal ordinances. If the user types in a new ordinance, I pop up a dialog to get default information for that ordinance and then write it back to the ordinance table. It didn't make sense to me to use a subform since the citation can only have one ordinance associated to it.

Follow up question - I set the form that is called to Modal = Yes so the user cannot do anything outside the form until they either OK or Cancel it. But in watching the processing, control is passed back to the calling function before the user OK/Cancels.

Sample code:
' Ok, lets look and see if we have a new ordinance or if this is an existing one
Set db = CurrentDb()
Set rst = db.OpenRecordset(strQuery, , dbReadOnly)
If (rst.RecordCount < 1) Then
' We have a new ordinance
' Name of the add ordinance form
strAddOrdinanceForm = "frmAddOrdinance"
' Set the calling parameters for the form
strArgs = Me.cmbOrdinance
' Open the add ordinance form
DoCmd.OpenForm strAddOrdinanceForm, , , , , , strArgs '-- 7th argument
MsgBox ("back in cmbOrdinance BeforeUpdate")

End If

I get the MsgBox text while the AddOrdinance form is still displayed. What I'm trying to do is add a new entry into the Ordinance table with the AddOrdinance form and then have those values pushed back to this calling form as defaults for several of the currency fields. I can't setup a join between the tables and simply leave the data in the Ordinances table because over time the currency amounts will change and I don't want legacy values to change (lots of people would get letters stating they were past due on their traffic tickets =) )

Thoughts on how to handle updating the calling form with the new default values once it gets control back? Maybe watch for the On Got Focus or Activate events for the form?

Safe travels,
Lution

 

[RuralGuy]

It sounds to me like you would be better served by using the NotInList event of the ComboBox.

 

[lution]

Thanks RuralGuy. It pays to read all the details, I missed that you had to lock results to values in the list to get the event to fire. Once I changed that it works like a charm.