Message Box events

[mba_110]

Hi everyone,

Hope you doing fine.

below is working code for message box, i want to amend the else portion of message box when VB Yes than it has to check whether the exchange rate is exist or not if exist then it should open the frmRecordExhRates with its values and dont create new entry, in below code its open with currency and date value with rate field blank but there is already rate on this date, i want him to populate that rate in fields of frmRecordExhRates when its open by vb Yes in else part of if condition.

Private Sub ExchangeRate_BeforeUpdate(Cancel As Integer)
Dim ERate As Integer
Dim VBAnsw As String
Dim sArgs As String
sArgs = Me.Currency & ";" & Me.TransactionDate

ERate = Nz(DLookup("[Rate]", "tblExchangeRates", "[Currency] = '" & Me.Currency & "' And [ExhDate] = #" & Me.TransactionDate & "#"), 0)
If Me.ExchangeRate <> ERate Then
VBAnsw = MsgBox("This rate is not found in exchange rate records!" & vbCrLf & vbCrLf & _
"Do you want to keep the rate limited to this transaction Only?", vbYesNo, "Warning")
If VBAnsw = vbNo Then
Cancel = True

Else

VBAnsw = MsgBox("You have changed this currency's exchange rate" & vbCrLf & vbCrLf & _
"Would you like system to change the rate for all transactions on this date?", vbYesNo, "Warning")
If VBAnsw = vbYes Then
DoCmd.OpenForm "frmRecordExhRates", acNormal, , , acFormAdd, acDialog, sArgs
Debug.Print sArgs
End If
End If
End If

End Sub

 

[June7]

Glad you posted code in CODE tags. But really should use indentation in code to make it easier to read.

You are passing data with sArgs. What do you do with it? Could also pass the rate info.

 

[essaytee]

You could achieve the update of your exchange rate table without opening a form by executing some SQL code directly.

I mention this as I believe the user experience will be far better.

The SQL code required is the INSERT INTO statement.

Check the link out for the component parts for the statement, apply it to your needs, you know what table is to be updated, you know what field needs to be updated. Then execute that SQL statement, presto, the table is updated, and the user hasn't left the current form.

I only suggest this as an alternative approach, by no means am I implying your approach is wrong, it's not.

 

[mba_110]

Args are identifying what date and what currency i am looking for, while opening exchange rate form, so that i dont need to put it manually.

Secondly my query is that if exchange rate is already there in a exchange rate table for selected date and currency then the field of rate on exchange rate form should be filled with args that i am passing from main form.

So that my changes will be applied to a saved record and not a new record.

 

[essaytee]

...snip

Secondly my query is that if exchange rate is already there in a exchange rate table for selected date and currency then the field of rate on exchange rate form should be filled with args that i am passing from main form.

So that my changes will be applied to a saved record and not a new record.

Then check out the SQL UPDATE statement. This deals with records already created but is the same principle I alluded to earlier in relation to the INSERT INTO.

 

[mba_110]

I think you did not understand what i want here, i dont want sql to update or insert into this information user have to do it manually, i just want to give him time to think what he is doing and what are the implications for the action while he update the rate manually on exchange rate form.

Arg is passing the values (Currency & Date) based on that values just need the code to show the existing rate on exchange rate form while its open with vb Yes thats it.

 

[Gasman]

So locate the record for that date and currency.?

 

[essaytee]

I think you did not understand what i want here, i dont want sql to update or insert into this information user have to do it manually, i just want to give him time to think what he is doing and what are the implications for the action while he update the rate manually on exchange rate form.

Arg is passing the values (Currency & Date) based on that values just need the code to show the existing rate on exchange rate form while its open with vb Yes thats it.

I only suggested an alternative approach that meets your requirements.

 

[mba_110]

I dont know how to add that criteria in this code, other than this i know how to do it in plain dlookup.

 

[Gasman]

I dont know how to add that criteria in this code, other than this i know how to do it in plain dlookup.

Why are you passing in Date and Currency to this form.?

I would have expected that would be to locate the record for that data.?

However if you are then looking to change all the transactions on that date for that currency to the new rate, then I would have thought you would need to filter for that data and only supply that in the form on that occasion.?

You would still need the new rate from somewhere, so if the user in the form decides, yes, I do want to the new rate, then you could update all those records, either by a recordset or query.

 

[mba_110]

Yes it will update all rate for the those transactions which is using this rate before or assigned to this rate it will be a big change, however if i dont want to pass the value through arg then i would need to open the exchange rate for to a specific record where currency and transactions date are equal with exchange rates table currency and record date so, that when user press yes then he will see the old rate their and can change it to new rate and this will apply to all connected transactions which is getting the rate for this date and currency.

Something like, but not sure its correct way.

DoCmd.OpenForm "frmRecordExhRates", , , me.TransactionDate = "# & Exhdate#", me.Currency = '" & Currency"'

 

[Gasman]

The criteria for the form is the same as that for SQL but without the WHERE

https://docs.microsoft.com/en-us/office/vba/api/access.docmd.openform

 

[mba_110]

I am unable to get it right, sArgs should be include or not?

DoCmd.OpenForm "frmRecordExhRates", , , TransactionDate = #" & Exhdate "#", Currency = '" & Currency "'", sArgs

 

[June7]

Concatenation is not correct as well as misplaced sArgs.

DoCmd.OpenForm "frmRecordExhRates", , , "TransactionDate = #" & Me.Exhdate & "# AND Currency = '" & Me.Currency & "'", , , sArgs

Currency is a reserved word. Should not use reserved words as name for anything.

 

[mba_110]

Thanks but it still giving syntax error.

 

[essaytee]

Thanks but it still giving syntax error.

What have you changed, show everyone, then comment, it still doesn't work.

Remove the where clause, see if the form opens, if it does, at least you know the Where clause is the issue.

Sent from my SM-G950F using Tapatalk

 

[mba_110]

form is open without where clause i have posted the working code before, the problem in where clause since i try to add its giving error.

Full code is mentioned above "essaytee" please check first before firing up .

 

[essaytee]

form is open without where clause i have posted the working code before, the problem in where clause since i try to add its giving error.



Full code is mentioned above "essaytee" please check first before firing up .

I disagree. The post prior to where you say you still have the error suggested reserved names maybe the issue. I merely suggested you post the code that you tried. The docmd bit. I wasn't being antagonistic, I'm sorry you felt that.

Sent from my SM-G950F using Tapatalk

 

[Gasman]

Not an attitude to elicit help.?

You have history of changing code and not posting the latest.

I will say this again, for the nth time.

Build your WHERE clause as a string then debug.print it and that will show you where you are going wrong.:banghead:

You could even post it so we can see the latest reincarnation of the code.?

form is open without where clause i have posted the working code before, the problem in where clause since i try to add its giving error.

Full code is mentioned above "essaytee" please check first before firing up .

 

[June7]

Yes, you should post the exact code you attempted, not just say suggested code errors. The code I suggested has correct syntax.