asign var velue with sql

[mchoud]

Dear all

I have a problem with this code, what is the mistake

set rst=dbs.OpenRecordset ("SELECT bincard.contro_id FROM bincard WHERE (((bincard.contro_id)="&me.contro_id&"))),

Could you please help

Thanks
mchoud

 

[Fizzio]

You have a missing quote in the SQL, try

set rst=dbs.OpenRecordset ("SELECT bincard.contro_id FROM bincard WHERE (((bincard.contro_id)= " & me.contro_id & ")))"

One question though, why are you opening a recordset that is only going to return one value that you already have available on your form?

If this does not fix it, post the error you are getting.

 

[mchoud]

You have a missing quote in the SQL, try

set rst=dbs.OpenRecordset ("SELECT bincard.contro_id FROM bincard WHERE (((bincard.contro_id)= " & me.contro_id & ")))"

One question though, why are you opening a recordset that is only going to return one value that you already have available on your form?

If this does not fix it, post the error you are getting.

Hi Fizio
Truly i have would like to cek for every entered data, in case the entered data has present at the table i want to give a message box, this is my complete code,
or may be you have efficient way for it.

Private Sub contro_id_BeforeUpdate(Cancel As Integer)
'Dim dbs As Database, rst As DAO.Recordset
'Set dbs = CurrentDb

Dim dbs As Database, rst As DAO.Recordset
Set dbs = CurrentDb
set rst=dbs.OpenRecordset ("SELECT bincard.contro_id FROM bincard WHERE (((bincard.contro_id)= " & me.contro_id & ")))"


if If IsNull(rst) = false Then
Else
'MsgBox "this document number is already in use, please use another number", vbExclamation
'DoCmd.CancelEvent
'End If

End Sub

 

[Fizzio]

Simpler, less complicated would be

If contro_id is an integer then;

If Dcount("[contro_id]", "bincard", "[contro_id] = " & me.contro_id) > 0 then
MsgBox "this document number is already in use, please use another number", vbExclamation 
cancel = true
end if

If contro_id is text then;

If Dcount("[contro_id]", "bincard", "[contro_id] = '" & me.contro_id) & "'" > 0 then
MsgBox "this document number is already in use, please use another number", vbExclamation 
cancel = true
end if

it maybe because contro_id is a text field. I automatically assumend it would be an long integer autonumber

does this help?

 

[mchoud]

Hi Fizio
When i try using you above code, ther is a run time error 3075 appeared.
Syntax error in string in query expresion '[contro_id]='11'

11 is new entered value.

What is it mean ?
Thanks

 

[Fizzio]

Pesky brackets!

this is correct

If Dcount("[contro_id]", "bincard", "[contro_id] = '" & me.contro_id &  "'") > 0 then
MsgBox "this document number is already in use, please use another number", vbExclamation 
cancel = true
end if

 

[mchoud]

Hi Fizio
Thanks very much about your quick solution, it has functioned as required.

If you don't mind i want to ask about the following code, why when i using this code, there is a message about data type mismatch ?

Private Sub contro_id_BeforeUpdate(Cancel As Integer)

Dim rst As Recordset
Set rst = CurrentDb.OpenRecordset("SELECT contro_id from bincard where [contro_id]= '" & Me.contro_id) & "'"
If rst.BOF And rst.EOF Then
Else
MsgBox "this document number is already in use, please use another number", vbExclamation
DoCmd.CancelEvent
End If


End Sub

 

[Fizzio]

This line:

Set rst = CurrentDb.OpenRecordset("SELECT contro_id from bincard where [contro_id]= '" & Me.contro_id) & "'"

should read:

Set rst = CurrentDb.OpenRecordset("SELECT contro_id from bincard where [contro_id]= '" & Me.contro_id & "'")

 

[mchoud]

Hi Fizzio

Thanks about your help, all of that code has working as required.

Thanks once again

Regards
Mchoud