Updating Fields on Form Open

[NCRStinks]

Hi Every1

I have only just started working on databases very recently - and I am not at the technical stage yet either.

I am creating a database to integrate into sage line 50. This bit is fine, its the "user friendly" side I'm having trouble with.

I have a form with the main invoice details (which is a sub form on the customer account). I need another form to enter the invoice message and breakdown of materials and labour. I would like to add a button on the subform to open a new form but to have already entered the invoice number.

I have tried already the microsoft built in options, but that only appears to work for data which has already been entered.

I would be grately appreciative if anyone could help.

Thanks

Dan

[FishVal]

Hi Every1

I have only just started working on databases very recently - and I am not at the technical stage yet either.

I am creating a database to integrate into sage line 50. This bit is fine, its the "user friendly" side I'm having trouble with.

I have a form with the main invoice details (which is a sub form on the customer account). I need another form to enter the invoice message and breakdown of materials and labour. I would like to add a button on the subform to open a new form but to have already entered the invoice number.

I have tried already the microsoft built in options, but that only appears to work for data which has already been entered.

I would be grately appreciative if anyone could help.

Thanks

Dan

Hi!

Let me straight it out. The form is opened incorrectly only on a record you've just entered, there is no problem with records entered previously. This is completely normal situation bcz Access saves new record when you move to another one or a form looses focus.

If so, the cure is easy. Just add

DoCmd.RunCommand acCmdSaveRecord

before DoCmd.OpenForm ... in button_click sub.

[NCRStinks]

Hi There,

Unfotunately not. Just to clarify I have 3 tables
* Invoice (Main details that I export to Sage)
* Invoice Details (main text string)
* Invoice Breakdown

These are all linked by the invoice number. I have created a form with the account details, and as a subform from that has the main invoice details. In the subform I have added a button using the access wizards to open a form (invoice details and invoice breakdown as a subform). But it does not link the invoice to the invoice details form.

Thanks for your help with this!

Dan

[FishVal]

Hi There,

Unfotunately not. Just to clarify I have 3 tables
* Invoice (Main details that I export to Sage)
* Invoice Details (main text string)
* Invoice Breakdown

These are all linked by the invoice number. I have created a form with the account details, and as a subform from that has the main invoice details. In the subform I have added a button using the access wizards to open a form (invoice details and invoice breakdown as a subform). But it does not link the invoice to the invoice details form.

Thanks for your help with this!

Dan

Plz post the sub handling button_click event.

[NCRStinks]

Plz post the sub handling button_click event.

Private Sub Command23_Click()
On Error GoTo Err_Command23_Click
    Dim stDocName As String
    Dim stLinkCriteria As String
  stDocName = "002b Transaction Details"
    stLinkCriteria = "[Invoice Ref]=" & "'" & Me![Invoice No] & "'"
    DoCmd.OpenForm stDocName, , , stLinkCriteria
Exit_Command23_Click:
    Exit Sub
Err_Command23_Click:
    MsgBox Err.Description
    Resume Exit_Command23_Click
End Sub

[FishVal]

Private Sub Command23_Click()
On Error GoTo Err_Command23_Click
Dim stDocName As String
Dim stLinkCriteria As String
stDocName = "002b Transaction Details"
stLinkCriteria = "[Invoice Ref]=" & "'" & Me![Invoice No] & "'"
DoCmd.OpenForm stDocName, , , stLinkCriteria
Exit_Command23_Click:
Exit Sub
Err_Command23_Click:
MsgBox Err.Description
Resume Exit_Command23_Click
End Sub

Is [Invoice No] text type field in table?

[NCRStinks]

Is [Invoice No] text type field in table?

Yes, the invoice number is manually entered. This links the two tables 001 Transaction Details (Invoice data to sage) and 002 Transaction Details (Invoice Breakdown)

[FishVal]

Yes, the invoice number is manually entered. This links the two tables 001 Transaction Details (Invoice data to sage) and 002 Transaction Details (Invoice Breakdown)

This definitely should work. Tell me please what you've actually meant in msg#1 ?

I have tried already the microsoft built in options, but that only appears to work for data which has already been entered.

Does this mean that problem occurs with new entries only?
Just to make a sence.

[NCRStinks]

This definitely should work. Tell me please what you've actually meant in msg#1 ?



Does this mean that problem occurs with new entries only?
Just to make a sence.

Sorry, I'm quite confused with all this!

On opening the new form and entering information. The info goes into the new table but not in the connecting fields ie the invoice number.

The fields are there with the information, but on re-opening the form it does not show relevant data because it isnt automatically updating the invoice number in the next table.

[FishVal]

Ok. Sorry, I had to realize it from the very beginning. Try the following code.

Private Sub Command23_Click()
 
    On Error GoTo Err_Command23_Click
 
    Dim stDocName As String
    Dim stLinkCriteria As String
 
    stDocName = "002b Transaction Details"
    stLinkCriteria = "[Invoice Ref]=" & "'" & Me![Invoice No] & "'"
 
    With DoCmd
        .RunCommand acCmdSaveRecord
        .OpenForm stDocName, , , stLinkCriteria
    End With
 
    Forms![002b Transaction Details]![Invoice Ref].DefaultValue = Me![Invoice No]
    ' Assuming form [002b Transaction Details] 
    ' has control named [Invoice Ref] bound to [Invoice Ref] table field
 
Exit_Command23_Click:
    Exit Sub
Err_Command23_Click:
    MsgBox Err.Description
    Resume Exit_Command23_Click
 
End Sub

[NCRStinks]

Ok. Sorry, I had to realize it from the very beginning. Try the following code.

Private Sub Command23_Click()
 
    On Error GoTo Err_Command23_Click
 
    Dim stDocName As String
    Dim stLinkCriteria As String
 
    stDocName = "002b Transaction Details"
    stLinkCriteria = "[Invoice Ref]=" & "'" & Me![Invoice No] & "'"
 
    With DoCmd
        .RunCommand acCmdSaveRecord
        .OpenForm stDocName, , , stLinkCriteria
    End With
 
    Forms![002b Transaction Details]![Invoice Ref].DefaultValue = Me![Invoice No]
    ' Assuming form [002b Transaction Details] 
    ' has control named [Invoice Ref] bound to [Invoice Ref] table field
 
Exit_Command23_Click:
    Exit Sub
Err_Command23_Click:
    MsgBox Err.Description
    Resume Exit_Command23_Click
 
End Sub

Thanks so much! After a little playing around I got it working - I just took the ".defaultvalue" out of line 16.

Again thanks so much!

[FishVal]

Thanks so much! After a little playing around I got it working - I just took the ".defaultvalue" out of line 16.

Again thanks so much!

Hi!
I'm glad you've made it work. I just want to straight out something.

Solution with .DefaultValue setting emulates Access Form/Subform relationships:

• allows to add multiple records with the same FK value
• does not create a new record before user has entered data

Your solution with .Value setting:

• creates a new record when Form2 is opened, if a user will not enter a data, then an empty record will be saved to table, you should take care to delete this record
• does not allow to add multiple records with the same FK value

[NCRStinks]

Hi!
I'm glad you've made it work. I just want to straight out something.

Solution with .DefaultValue setting emulates Access Form/Subform relationships:

• allows to add multiple records with the same FK value
• does not create a new record before user has entered data

Your solution with .Value setting:

• creates a new record when Form2 is opened, if a user will not enter a data, then an empty record will be saved to table, you should take care to delete this record
• does not allow to add multiple records with the same FK value

Hi There

I've just noticed the fact it creates empty records. When ".DefaultValue" is in the code #Name? appears in the invoice reference field?

It only appears to work without .DefaultValue.

Any ideas?

Thanks

Dan

[FishVal]

Hi There

I've just noticed the fact it creates empty records. When ".DefaultValue" is in the code #Name? appears in the invoice reference field?

It only appears to work without .DefaultValue.

Any ideas?

Thanks

Dan

Post your sub as it looks now.

[NCRStinks]

Post your sub as it looks now.

Since last spoke re-arranged a bit (forms different names etc)

 Private Sub Command12_Click()
 
    On Error GoTo Err_Command12_Click
 
    Dim stDocName As String
    Dim stLinkCriterea As String
 
    stDocName = "002b Inv"
    stLinkCriterea = "[InvoiceReference]=" & "'" & Me![Invoice No] & "'"
 
    With DoCmd
        .RunCommand acCmdSaveRecord
        .OpenForm stDocName, , , stLinkCriterea
    End With
 
    Forms![002b Inv]![InvoiceReference] = Me![Invoice No]
    ' Assuming form [002b Inv]
    ' has control named [InvoiceReference] bound to [InvoiceReference] table field
 
Exit_Command12_Click:
    Exit Sub
Err_Command12_Click:
    MsgBox Err.Description
    Resume Exit_Command12_Click
 
End Sub
 

[FishVal]

Since last spoke re-arranged a bit (forms different names etc)

 Private Sub Command12_Click()
 
    On Error GoTo Err_Command12_Click
 
    Dim stDocName As String
    Dim stLinkCriterea As String
 
    stDocName = "002b Inv"
    stLinkCriterea = "[InvoiceReference]=" & "'" & Me![Invoice No] & "'"
 
    With DoCmd
        .RunCommand acCmdSaveRecord
        .OpenForm stDocName, , , stLinkCriterea
    End With
 
    Forms![002b Inv]![InvoiceReference] = Me![Invoice No]
    ' Assuming form [002b Inv]
    ' has control named [InvoiceReference] bound to [InvoiceReference] table field
 
Exit_Command12_Click:
    Exit Sub
Err_Command12_Click:
    MsgBox Err.Description
    Resume Exit_Command12_Click
 
End Sub
 

Ok.
Change line #16 to

Forms![002b Inv]![InvoiceReference].DefaultValue = "'" & Me![Invoice No] & "'"

[NCRStinks]

Ok.
Change line #16 to

Forms![002b Inv]![InvoiceReference].DefaultValue = "'" & Me![Invoice No] & "'"

Thats great, thanks ever so much for your help!

Dan

[FishVal]

Thats great, thanks ever so much for your help!

Dan

Glad you've made it work.