Before update formula

  • Thread starter Thread starter Bongard
  • Start date Start date
B

Bongard

I am trying to allow the user to make multiple entries on a form
(numerical) that must sum to zero. This is what I have VBA to validate
this before the button can be clicked

Private Sub Form_BeforeUpdate(Cancel As Integer)


'If SumOfTotalPrice must be =0:
If Me.sum <> 0 Then
MsgBox " Sum of Total Entries must be
equal to 0." & vbCrLf & "Please check to make sure that you have
entered the right dollar amounts"
'Set cancel to true so no update to the table
Cancel = True
End If


End Sub

Right now, it won't work when I click the "add record" button and it
did in the past. What screwed it up was that I also wanted to run an
append query using this same button but couldn't figure out how to do
it. Can someone help me at least get this formula working again as a
check for the button so that the records cannot be added to the table
until the amounts sum to 0. Also, if anyone knows how I would greatly
appreciate if you would help me get the append query macros working as
well.

Thanks a lot!
 
Barry said:
The code you have here should work. What is the code under the button?
Click to expand...
Thats the wierd thing about it. I tried running some Macro's on the
click of the button in the event tab but now I have deleted those
macros from the event tab so that they don't run anymore. That way I
was hoping to get my condition =0 else msgbox to come back. that
condition works but it only shows the message when I try to exit the
form...instead of when I click the button. So I deleted that button and
just inserted a new command button with the insert record as its
operation and that part works again.

So really I just need an idea to get this macro that runs the append
query's to work on the click of my button while the condition (sum=0)
still works.


Thanks in Advance!
 
Dim strSQL as String
strSQL = "Insert Into MyTable(MyField1, MyField2) Values('" & MyFormValue1 &
"', '" & MyFormValue2 & "')"
CurrentDb.Execute strSQL, dbFailOnError

This assumes that the form values arer string values, thus the single quotes
surrounding the variables.

If the form is bound to the table, you obviously don't need an append query,
though.

Barry
 
Thanks Barry. The form that is active "Transactions" is bound to the
"Transactions" table (with different naming conventions of course) and
I need the date being entered in the fields "Amount 1" and "Amount 2"
to also be copied into the respective fields in the Transactions table
as well.
I guess my question is then where do I insert this statement that you
have given me here? And the part CurrentDb...is that the name of my
current database or another command?

Thanks,
 
Again, in a bound form, records entered into textboxes will automatically
save to the table. I only mention this in case it eliminates the need for an
append query. This is as basic as Access gets and is always the simplest and
best way to do things. An append query might be needed if you are working
with an different table than the one your form is bound to.

The code I suggested should go wherever you want to do the append,
presumably under a button click event. The CurrentDb object refers to the
current database; it's not a named variable or function.

Barry
 
Ok I have that in the 'Add Records' button event and this is what the
whole button's command looks like:

Private Sub Add_Records_Click()

On Error GoTo Err_Add_Records_Click


DoCmd.GoToRecord , , acNewRec

Exit_Add_Records_Click:
Exit Sub

Err_Add_Records_Click:
MsgBox Err.Description
Resume Exit_Add_Records_Click

Dim strSQL As String
strSQL = "Insert Into [tbl_Transactions](Loan Number, Amount) Values(&
Loan Number &,& Amount 1 &)"
CurrentDb.Execute strSQL, dbFailOnError


End Sub

With the last part obviously being the part that we discussed. It isn't
giving me any error messages but it is not copying those values into
the tbl_transactions. The Entries from the form are posting normally to
the table tbl_LTLT however.

Any more suggestions or any erros that you see?

Thanks,
brian
 
There are several problems. Firstly, you pasted the code after the error
handler. It will never execute there. It needs to be put before the
Exit_Add_Records_Click: statement.

It looks like your code goes to a new record with DoCmd.GoToRecord , ,
acNewRec.
This tells me the form is bound to the table. If ou're trying to insert a
record in that table, you don't need the Insert statement. When you type
values into controls for a new record on a form, you're entering the record.
When you move away from that new record or close the form, the record is
saved. Am I missing something?

Barry
 
This form that the user will be updating will be updating the table
tbl_LTLT, and it is doing so just fine. What I am trying to accomplish
is to get two of the fields on the frm_LTLT input by the user to be
also input into the table tbl_transactions, sorry if I wasn't clear on
that earlier.

I tried pasting my statement before the Exit_Add_Records_Click and I
got this message when trying to add the record.
"SYNTAX error in INSERT INTO statement"

My VBA looks like this....

Private Sub Add_Records_Click()

On Error GoTo Err_Add_Records_Click

DoCmd.GoToRecord , , acNewRec

Dim strSQL As String
strSQL = "Insert Into [tbl_Transactions](Loan Number, Amount) Values(&
Loan Number &,& Amount 1 &)"
CurrentDb.Execute strSQL, dbFailOnError

Exit_Add_Records_Click:
Exit Sub

Err_Add_Records_Click:
MsgBox Err.Description
Resume Exit_Add_Records_Click

End Sub


I am really stuck
 
Ok. There are a few problems with your syntax. Try this:

strSQL = "Insert Into [tbl_Transactions]([Loan Number], Amount) Values(" &
[Loan Number] & "," & [Amount 1] & ")"
CurrentDb.Execute strSQL, dbFailOnError

I'm assuming that [Loan Number] is the name of a control or field on your
form. The same with [Amount 1].

Any time you have a space in a field name (which you should never do, by the
way), you need to surround the name in square brackets.

Barry
 
We'll I think we are getting closer--It is now posting an entry to the
transactions table with the timestamp on it, but there are no values
being entered into either field. My VBA now looks like this:

On Error GoTo Err_Add_Records_Click

DoCmd.GoToRecord , , acNewRec

Dim strsql As String

strsql = "Insert Into [tbl_Transactions]([Loan Number], Amount)
Values(" & [LN1] & "," & [AMOUNT 1] & ")"
CurrentDb.Execute strsql, dbFailOnError

Exit_Add_Records_Click:
Exit Sub

Err_Add_Records_Click:
MsgBox Err.Description
Resume Exit_Add_Records_Click


I'm not sure why no entry is being made.

Thanks again for all your help!
-Brian
 
It sounds like [LN1] and [Amount 1] are null when this code runs. Is this
code in the right place to pick up those values?
To test my theory, put the following code before CurrentDb.Execute:

Debug.Print strSQL

Open the immediate window (Ctrl-G) and click the button. It will print the
sql statement with values to the immediate window so you can see what it's
trying to execute. If there are no values in these two fields, I think you
have the code in the wrong place. Maybe it should be in the form's
AfterUpdate event so it runs when the main record is saved.

Barry

Bongard said:
We'll I think we are getting closer--It is now posting an entry to the
transactions table with the timestamp on it, but there are no values
being entered into either field. My VBA now looks like this:

On Error GoTo Err_Add_Records_Click

DoCmd.GoToRecord , , acNewRec

Dim strsql As String

strsql = "Insert Into [tbl_Transactions]([Loan Number], Amount)
Values(" & [LN1] & "," & [AMOUNT 1] & ")"
CurrentDb.Execute strsql, dbFailOnError

Exit_Add_Records_Click:
Exit Sub

Err_Add_Records_Click:
MsgBox Err.Description
Resume Exit_Add_Records_Click


I'm not sure why no entry is being made.

Thanks again for all your help!
-Brian

Barry said:
Ok. There are a few problems with your syntax. Try this:

strSQL = "Insert Into [tbl_Transactions]([Loan Number], Amount) Values(" &
[Loan Number] & "," & [Amount 1] & ")"
CurrentDb.Execute strSQL, dbFailOnError

I'm assuming that [Loan Number] is the name of a control or field on your
form. The same with [Amount 1].

Any time you have a space in a field name (which you should never do, by the
way), you need to surround the name in square brackets.

Barry
Click to expand...
Click to expand...
 
Unbelievable I actually got it to work! Actually you got it to work and
I plugged it in... it ended up working correctly in the forms
AfterUpdate. I thank you so much for your help. Now I just have to
write a big if statement to get all 5, LN's and Amounts to post at the
same time...That could be a struggle but thanks for your time I
appreciate it.

-Brian
 
Back
Top