compare values from tbl1 to tbl2 in a FORM??

Hi, I have a tbl1 fields: clientID, DueDate and tbl2 fields: clientID, DatePayed. I have managed to copy both values from tbl1 to tbl2 with an INSERT query!!! Anyhoods, my concerns is the follwing;

In form1 I have the fields tbl1.clientID, tbl1.DueDate and a 'payed' button. Once i click on the 'payed' button, the values are inserted into tbl2. clientID, tbl2.DatePay and are then displayed in another form2.

Now, to avoid duplication, how can I compare in form1 once clicking on the'payed' button, to not to insert the values tbl1.clientID, tbl1.DueDate into tbl2 if these records already exist from the first time I copyed them values?? I was thinking of an IF statement on the 'payed' button code to validate compare the tbl1.DueDate with the tbl2.DatPayed if it already was copied or exists???

On form1 'payed' button I have the code;

IF Me.DueDate='how can i compare the value of tbl2.DatePayed' THEN
msgbox"Can't insert date payed, it already has been entered!"
exit sub
ELSE
insert .............copy values here tbl1 to tbl2........
END IF

If you have any idea or had similar problems or you were stuck, please advise me as I am running out of time!!! Thank you!!

Why do you handle due dates and payment dates in separate tables? Is this something you must stick to?

Based on the information you posted I think it's enough to handle them in one table:

ClientID
DueDate
DatePaid

Let me know.

Hey dude, TBArpi thanks, having the info in one table woould be much better but it already has been made this way, is this still possbile in an IF statement,

IF Me.DueDate='how can i compare the value of tbl2.DatePayed here?' THEN
msgbox"Can't insert date payed, it already has been entered!"
exit sub
ELSE
insert .............copy values here tbl1 to tbl2........'this part is ok
END IF

Yes, it's possible, but...

Anyway, it's not my mission to judge your way...

Modify your code to this:

if not isnull(dlookup("ClientID","tbl2","ClientID=" & ClientID)) then 'if there's an existing record in tbl2 with ClientID of the current record in the form
msgbox"Can't insert date payed, it already has been entered!"
else
' here's your "copy" code, which you said is OK.
endif

Hey TBArpi, thanks for the DLookup function, and sorry for the dealy....am here in northern africa modifying and designing this db for an ngo....so internet connection is not the best....i have one more question regarding the Dlookup function syntax which is Dlookup("field value to search on", "tbl", "criteria"), now can the criteria be based on three different things in order to return the value in the searched field?? I entered this code but it was giving me problems....notice in the criteria section of Dlookup function I have three coparisons........Is this possible??

If IsNull(DLookup("[date_pay]", "tblPayments", "[date_pay]=" &
Me.due_date.Value And "[clientID]=" & Me.clientID.Value
And "[creditID]=" & Me.creditID.Value)) Then

MsgBox "Can't insert payment, already exists", vbOKCancel
Else
strSQL = "INSERT INTO Payments (clientID,creditID,
date_pay) " & _
"VALUES ('" & Me.clientID & "', " & Me.creditID & ", #" &
Me.date_echeance & " #, " & Me.valeurquota & ", 'P');"

DoCmd.RunSQL strSQL

End If

First, I think you missed the "&" operator in some places...

If IsNull(DLookup("[date_pay]", "tblPayments", "[date_pay]=" &
Me.due_date.Value & " And [clientID]=" & Me.clientID.Value &
" And [creditID]=" & Me.creditID.Value)) Then

Second, Date expressions in an SQL statement or a where condition are only accepted if they're in "#mm/dd/yyyy#" format.
Check the URL below where I posted two functions that convert Date format to "#mm/dd/yyyy#" format as string.
http://forums.aspfree.com/showthrea...43596#post43596

So you need to change Me.due_date.Value to GetDateStringForSQL(Me.due_date)

Third, as I remember, you cannot refer to a control's property unless the control has the focus. So you may have problems if you refer to Me.due_date.Value instead of just using Me.due_date.

Hi again TBArpi, I have tried what you recommended me and for some reason I still get the same error 3464 that states’type mismatch in the expression criteria’ well, I have attached a little briefly zip file example notice that there are three forms. 1. FrmDueDates where the due dates are stored and displayed 2. frmPayDatesList where you enter a due date and the clients info is displayed along with the ‘pay’ button 3.frmClientsPayedRecord where it displays information of the paid payments from the (2.frmPayDatesList ‘pay’ button click)

If you can look at the code in the 2.frmPayDatesList ‘pay’ button in the If statement Dlookup(………) which is where the problem is at….one more thing, it seems to work OK with only one expression in the criteria section of the Dlookup function!!

I would very much appreciate it…………once again thank’s !!!

OK, I will have a look when I got home.

In the meantime, you can check the types. If the type of a variable or field is string or text in an SQL statement then you need to include extra apostrophes like this:

Dim SQLStatement As String
SQLStatement = "select blabla_field from blabla_table where blabla_field='" & blabla_control & "'"

because when it's executed, it should look like this:

select blabla_field from blabla_table where blabla_field='blabla_value'

except for a date criteria, because in spite of that it's a string type data, it shouldn't be between apostrophes.

The same for WhereCondition or Criteria.

i'll recheck it again, and once you have looked at it and find anything, please let me know, thanks again TBArpi, you've been a superb support!

The problem that caused the error was that the ClientID is a string type data and needed extra apostrophes which I was talking about in my earlier post.
I made some modifications as follows:
'******************* START OF YOUR ORIGINAL *******************
' If Not IsNull(DLookup("[date_pay]", "tblDatePayed", "[date_pay]=" & GetDateStringForSQL(Me.DueDate) & " And [clientID]=" & Me.clientID & " And [creditID]=" & Me.creditID)) Then
' MsgBox "Can't insert payment, it already has been entered!", vbOKOnly
' Else
' strSQL = "INSERT INTO tblDatePayed (clientID, creditID, date_pay, amount) " & _
' "VALUES ('" & Me.clientID & "', " & Me.creditID & ", #" & Me.DueDate & " #, 1000.00);"
'
' DoCmd.RunSQL strSQL
'******************* END OF YOUR ORIGINAL *********************
'******************* START OF MY CODE *******************

If Not IsNull(DLookup("[date_pay]", "tblDatePayed", "[date_pay]=" & GetDateStringForSQL(CDate(Me.DueDate)) & " And [clientID]='" & Me.clientID & "' And [creditID]=" & Me.creditID)) Then
MsgBox "Can't insert payment, it already has been entered!", vbOKOnly
Else
strSQL = "INSERT INTO tblDatePayed (clientID, creditID, date_pay, amount) " & _
"VALUES ('" & Me.clientID & "', " & Me.creditID & ", " & GetDateStringForSQL(CDate(Me.DueDate)) & ", 1000.00);"

MsgBox strSQL ' USE THIS TO CHECK IF SQL STATEMENT SYNTACTICALLY CORRECT

DoCmd.RunSQL strSQL
'******************* END OF MY CODE *******************

I have tested it and worked OK.

Thanks TBArpi, I appreciate it......Everything is smoothly with this application now!!! All the best!!!!

Cheers,

Mauron.