Message Box events

[mba_110]

I am sorry if its count as attitude.

here is full code.

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", , , "TransactionDate = #" & Exhdate & "AND Currency = '" & Currency & "'", sArgs

Debug.Print sArgs
End If
End If
End If

End Sub

While working with update its get change but that is also very few times with continues post in same thread, won't happen ALWAYS.

 

[essaytee]

I am sorry if its count as attitude.



here is full code.

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", , , "TransactionDate = #" & Exhdate & "AND Currency = '" & Currency & "'", sArgs



Debug.Print sArgs

End If

End If

End If



End Sub

While working with update its get change but that is also very few times with continues post in same thread, won't happen ALWAYS.

I haven't dissected all of it. You may want to add a closing # around the date in the docmd command.

Also show us the result of your debug.print.

Sent from my SM-G950F using Tapatalk

 

[essaytee]

I haven't dissected all of it. You may want to add a closing # around the date in the docmd command.

Also show us the result of your debug.print.

Sent from my SM-G950F using Tapatalk

Oops, sorry, closing hash is there.

Sent from my SM-G950F using Tapatalk

 

[essaytee]

Oops, sorry, closing hash is there.

Sent from my SM-G950F using Tapatalk

Damn, sorry for quick posts, doing this from tapatalk, not ideal. Check closing hash in docmd.

Sent from my SM-G950F using Tapatalk

 

[Minty]

Try this , see if it helps.
Can you check your answers on other posts and see how many times it has been suggested that use Option Explicit at the top of EVERY code module?

Private Sub ExchangeRate_BeforeUpdate(Cancel As Integer)

Dim ERate As Integer
Dim VBAnsw As Integer
Dim sArgs As String

sArgs = Me.Currency & ";" & Me.TransactionDate

Debug.Print sArgs

ERate = Nz(DLookup("[Rate]", "tblExchangeRates", "[Currency] = '" & Me.Currency & "' And [ExhDate] = #" & Me.TransactionDate & "#"), 0)

Debug.Print "Rate " & ERate
Debug.Print "Ex Date " & Nz(Exhdate,"I forgot to declare this or set a value!") 

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", , , "TransactionDate = #" & Exhdate & "# AND Currency = '" & Currency & "'", sArgs

		End If
	End If
End If

End Sub

 

[Gasman]

I can't see any closing hash for the date.?
No space before the AND
Also should you not me using Me.Transactiondate and not exchDate which appears to a field in a table.?

You can Debug.Print sArgs, but cannot do it for a WHERE clause.?:banghead:

How you work out your If/EndIf matching is beyond me.

It does not apply in this particular case, but indenting your code makes it much easier to read.

 

[mba_110]

Thanks everyone for your posts, now its 26 posts in one thread and still the problem is same anyways, i have to solve this by myself.

I put option explicit at top of code module window and not at the event of button hence, you are not able to see it here.

 

[Minty]

Did you then try and compile your code? Because you haven't declared the variable Exhdate - and it would give you an error.

Or the code you have posted isn't what you are actually using.

 

[Gasman]

Whose fault is that.?

Trying to get relevant information from you is like trying to get blood from a stone.

Again, my inexperience might show here, but how does exhDate evaluate?, as I can see that you have 'exhdate' in the DoCmd, and the fact that it is not CamelCase and not declared, means access does not know what it is.

Again a Debug.Print would identify that in a heartbeat, yet you refuse to do it.:banghead:

And so we go to 29 posts.

Thanks everyone for your posts, now its 26 posts in one thread and still the problem is same anyways, i have to solve this by myself.

I put option explicit at top of code module window and not at the event of button hence, you are not able to see it here.

 

[mba_110]

Ok, now in final try.

in below code i have changed several stuff all variables are there now the problem is shifted to different portion of code no more docmd may be after i solve that one but, presently its with dlookup which is showing two errors invalid use of null after i add NZ to date field and data type mismatch before NZ for ExhDate field.

ExhDate = DLookup("[ExhDate]", "tblExchangeRates", "[ExhDate] = #" & Me.TransactionDate & "#")

I have attached the db so, that everyone will know i am telling the truth.

Private Sub ExchangeRate_BeforeUpdate(Cancel As Integer)
Dim ERate As Integer
Dim VBAnsw As String
Dim sArgs As String
Dim ExhDate As Date
Dim CCY As String
sArgs = Me.Currency & ";" & Me.TransactionDate
ExhDate = DLookup("[ExhDate]", "tblExchangeRates", "[ExhDate] = #" & Me.TransactionDate & "#")
CCY = DLookup("[CCY]", "tblExchangeRates", "[CCY] = '" & Me.Currency & "'")
ERate = Nz(DLookup("[Rate]", "tblExchangeRates", "[CCY] = '" & 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", , , "Currency = '" & CCY & "'" And TransactionDate = "#" & ExhDate & "#", sArgs

Debug.Print sArgs
         End If
    End If
End If

End Sub

 

[Gasman]

I get Unrecognised database format even with shift key, so that is me out.

However, I think you need to step back and take a breath.?

ExhDate = DLookup("[ExhDate]", "tblExchangeRates", "[ExhDate] = #" & Me.TransactionDate & "#")

All that does is get the ExhDate for a ExhDate that is equal Me.TransactionDate ??

Why not just use Me.TransactionDate ?

 

[June7]

As I said earlier, sArgs is in wrong position. There is extra quote marks.

DoCmd.OpenForm "frmRecordExhRates", , , "Currency = '" & CCY & "' And TransactionDate = #" & ExhDate & "#", , , sArgs

A textbox is bound to a field Names which doesn't exist.

Duplicate keyboard shortcuts on buttons.

Currency combobox references a table that doesn't exist.

Cannot edit frmCashBankEntrySub records when opened as subform.

Really should make better use of indentation in code.

 

[mba_110]

Sorry i forgot to add exchange rate table....its there now.

I will definitely see the indentation of code to make it easy to understand, as far as i know it help reading the code but its an optional stuff.

 

[June7]

Now there is a combobox bound to field Names that doesn't exist in form RecordSource and its RowSouce references a table not in the db.

However, the OpenForm code works. What exactly is the issue now?

I see "Conversion" misspelled as "Convertion".

 

[Gasman]

From what has been written so far, I think any help you can get is a good thing.

How do you write the OpenForm code?, as intelllisense prompts you for each parameter with its name, so where OpenArgs is meant to be is is obvious.

Sorry i forgot to add exchange rate table....its there now.

I will definitely see the indentation of code to make it easy to understand, as far as i know it help reading the code but its an optional stuff.

 

[mba_110]

The form is open from the post one, i dont have that issue.

The issue is form open with record that i have exchange date and currency same as subform fields selection which i am doing the event on it, so that no new record will be created when exchange form opens.

if subform mentioned currency and transaction date is found in tblExchangeRates then i should open to that particular record populating the fields on frmExchangerate with filled text boxes for that information, their is one field name [rate] that will be filled with that record's exchange rate, if not found that matching then ok with current criteria to add new.

More details are in first post already.

 

[Gasman]

Are we losing something in translation?

You are writing code which is syntactically incorrect, so will either not work at all, or not work in the way you think it will.?

I am talking about the misplaced sArgs argument that June7 pointed out a few posts ago.

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

 

[June7]

The sArgs placement seems to have been corrected in the latest database. The frmRecordExhRates opens filtered to record meeting criteria.

And another combobox with RowSource referencing a table not in db.

Don't think I can assist any more with this db. It is too dysfunctional.

 

[Gasman]

Hi June7,

It might well have been, but the o/p is keen to say something was in a previous post, whilst not paying attention to their own advice.:banghead:

My query was how is that happening, when intellisense literally guides you through the arguments.

That is something I rely on a lot when writing code for forms, reports etc.

Learn to walk before running springs to mind here.

 

[essaytee]

Check the attached Access file that I've modified. I created a module and in that module wrote a function "UpdateExchangeRate()". You pass into this function the following:

Currency Tag (eg. CAD, USD, whatever - string value)
Exchange Date (date of exchange rate - date value)
Exchange Rate (double value)

The function either updates an existing record or if it does not exist, adds a new record.

I have also included two other useful functions that take care of wrapping string values in quotes and converting dates to US style and wrapping in #.

Test this from the immediate window directly and you will see that the exchange rate table is updated or new record added.

As an exercise (hint) (from the immediate window) try and execute the following:

? UpdateExchangeRate("USD", date, 2)

Assuming the data in the table is as I uploaded it, an error will be returned but you should be able to resolve it at the table level. The function is not the cause of the error. As a hint, it's the "2" that is causing the problem.

Anyway, you would use this function at the form level, is called after you determine that the exchange rate table should be updated, if yes, then call the function.

I know this doesn't address your DoCmd.OpenForm routine, but is merely an alternative approach.