One Save button for form and sub form

[mba_110]

Hi

I am trying to figure out the way how i can control main form with sub form save and fields change event.

What i am trying to do is,

1. I want one button on main form (frmGeneralDataEntry) to save any changes made on sub form (frmTransactionsSub) which is part of main form as well as sub form.

2. Sub form do not record any changes until i click save button on main form.

3. If main form forcefully or accidentally close in abnormal way then what ever changes have been made for main form as well as sub form should not be recorded.

or i can create two save buttons one on main form and another on sub form and control sub form save button from main form save button?

 

[Ranman256]

you are making this harder than it needs.
there is no need for a button to save. Save is automatic when the user moves off the record.

otherwise you must create an unbound form, user makes changes, clicks save, transfer the data to the table.
This is a waste of time and effort since access saves it all for the user.

 

[mba_110]

Dear Ranman256

Does it cover all the points i mentioned in above post, i dont think so.

no backup for accidental input or change, if you know the procedure whether its hard or soft please mention.

 

[Minty]

You have to trust your users to make data input. If they make a mistake then they should be able to edit the record to correct it.

If they enter a completely useless record and it needs deleting then make that functionality available to a Admin user.

On top of all this make the data input as fool-proof as possible. Restrict data in text boxes to combo's where possible if you only have a fixed number of options, so that only a valid entry is possible. Use form before update events to check for required fields and valid entries.

 

[mba_110]

Ok, i have some issues with my following code, its working fine but not following the steps of event, may be i used wrong operators.

Dim VBAnsw As String
If IsNull(Me.ExchangeRate) Then
VBAnsw = MsgBox("Currency exchange rate is not found for selected transaction date !!!" & vbCrLf _
& "" & vbCrLf _
& "Do you want to record exchange rate for transaction date?", vbYesNo, "Warning")
If VBAnsw = vbYes Then
DoCmd.OpenForm "frmRecordExhRates"
Else
Me.ExchangeRate = DLookup("[Rate]", "tblExchangeRates", "[Currency] = '" & Me.Currency & "' And [ExhDate] = #" & Me.TransactionDate & "#")
End If
End If

In above code i want frmRecordExhRate should open with following.

frmTransactionsSub.Currency.value = frmRecordExhRates.Currency
frmTransactionsSub.ExhDate.value = frmRecordExhRates.ExhDate

 

[Minty]

The best way would be to pass those as values using OpenArgs.

You were shown exactly how to do that in another thread here : https://www.access-programmers.co.uk/forums/showthread.php?t=303428

 

[mba_110]

Yes you are right but, i am not passing string i am passing value of a field which can be anything (not fixed).

That procedure i did not apply in previous post which you are sharing here.

I did try with following

Private Sub Form_Load()

If Not Trim(Me.OpenArgs & " ") = "" Then
    Me.Currency = Split(Me.OpenArgs, ";")(0)
    If UBound(Split(Me.OpenArgs, ";")) = 1 Then
      Me.ExhDate = Split(Me.OpenArgs, "#;#")(1)
    End If
End If
End Sub

Dim VBAnsw As String
If IsNull(Me.ExchangeRate) Then
VBAnsw = MsgBox("Currency exchange rate is not found for selected transaction date !!!" & vbCrLf _
& "" & vbCrLf _
& "Do you want to record exchange rate for transaction date?", vbYesNo, "Warning")
If VBAnsw = vbYes Then
DoCmd.OpenForm "frmRecordExhRates", acNormal, , , acFormAdd, acDialog, Me.Currency.Value, Me.TransactionDate.Value
Else
Me.ExchangeRate = DLookup("[Rate]", "tblExchangeRates", "[Currency] = '" & Me.Currency & "' And [ExhDate] = #" & Me.TransactionDate & "#")
End If
End If

End Sub

 

[Minty]

Open args is one string value not multiples.

You need to create it as a string and use the split function to extract the information passed.

Split your code into sections to make it more obvious what you are doing;

    Dim VBAnsw As String
  [COLOR="Red"]  Dim sArgs As String
    
    sArgs = Me.Currency & ";" & Me.TransactionDate[/COLOR]
    Debug.Print sArgs
    
    If IsNull(Me.ExchangeRate) Then
       VBAnsw = MsgBox("Currency exchange rate is not found for selected transaction date !!!" & vbCrLf & vbCrLf _
            & "Do you want to record exchange rate for transaction date?", vbYesNo, "Warning")
        If VBAnsw = vbYes Then
            DoCmd.OpenForm "frmRecordExhRates", acNormal, , , acFormAdd, acDialog, [COLOR="red"]sArgs[/COLOR]
        Else
            Me.ExchangeRate = DLookup("[Rate]", "tblExchangeRates", "[Currency] = '" & Me.Currency & "' And [ExhDate] = #" & Me.TransactionDate & "#")
        End If
    End If

 

[mba_110]

Thanks Minty for your guidance.

I have one question about exchange rate usually the exchange rate is placed in decimal values up to 4 digits after full stop like 4.5271 and table is also accepting it.

But my question about form field which is making this value round figure lets say 4.5271 is became 5.000 i can restrict this from round figure and keep the value as it is stored in table like 4.5271

Anything you know how i can do that? , its make big difference in calculations.

 

[Minty]

What data format is the field in the table / form?
You would need to set it to Decimal instead of Integer to retain the fractional part of the number data.

 

[mba_110]

My tblExchangeRates [Rate] field have following.
Field size = Double
Field Format = (Blank)
Decimal Places = 4


My frmTransactionsSub [ExchangeRate] field have following.
Format = General Number
Decimal Places = 4

I cannot put decimal because field in table make it to round figure automatically, so i use double to input the decimal places.

 

[Minty]

You can specify the scale of decimal in the table. However have a read here for more information.
https://support.office.com/en-us/ar...0ad644f-946c-442e-8bd2-be067361987c#refnumber

General Number doesn't do what you think even if you specify a number of decimal places.

 

[mba_110]

I have no problem with table settings, but what ever i have settings on table field form field is not showing that it is rounding the figure which i dont want.

Show me settings of form.

My tblExchangeRates [Rate] field have following.
Field size = Decimal
Field Format = General number
Scale = 4
Decimal Places = 4


My frmTransactionsSub [ExchangeRate] field have following.
Format = General Number
Decimal Places = 4

 

[Minty]

I know - but General number won't display fractions. Change it to either standard or fixed in the form format property, with the decimal places setas you need.

 

[mba_110]

Yes but i tried all option available their nothing work out.

is it possible to sort out by any other way?

 

[Minty]

Well if it's a number data type, then what I suggested definitely works on the form for display purposes. If you are storing it as a general number then you will probably lose the fractional part.