VBA & SQL error

[nebon]

hallo guys, look I am not the guy that usually bug others, but I am now in a state where I need to ask someone for help ..
I have tried several methods to solve this problem, but with no result. anyway the problem is like this :
When you press the button add in the booking form the onclick even should have the following effect :
check in quryControl in field Status, if the status is having the integer 0 then add record else tell the user the capacity is reached. I have tried the following vba code :

Dim rst As ADODB.Recordset

Set rst = New ADODB.Recordset
rst.ActiveConnection = CurrentProject.Connection
rst.Properties("Jet OLEDB:Inconsistent") = True

rst.Open _
Source:="SELECT query1.status" & _
" FROM query1" & _
" WHERE (query1.show_id) = " & Me.SHOW_ID.Value, _
Options:=adCmdText

If rst!Status = 0 Then
MsgBox "Application Form Successfully Added"

Cancel = True
Else

MsgBox "ERROR: Too many members! This application Form is now being deleted"
End If

Set rst = Nothing

when this gets executed it would ALWAYS give me the following error : "no value given for one or more required parameters."

Would someone please help me ?

 

[Fizzio]

I cannot describe why you get this error. The SQL seems to grab the value before it has populated the form but try this as a possible solution.

Rather than use the direct reference in the SQL, set a variable to that value and use that in the SQL ie.

Dim rst As ADODB.Recordset, intShow_ID as integer

Set rst = New ADODB.Recordset 
rst.ActiveConnection = CurrentProject.Connection 
rst.Properties("Jet OLEDB:Inconsistent") = True 
intShow_ID = Me.SHOW_ID.Value

rst.Open _ 
Source:="SELECT query1.status" & _ 
" FROM query1" & _ 
" WHERE (query1.show_id) = " & intShow_ID, _ 
Options:=adCmdText 

If rst!Status = 0 Then 
MsgBox "Application Form Successfully Added" 

Cancel = True 
Else 

MsgBox "ERROR: Too many members! This application Form is now being deleted" 
End If 

Set rst = Nothing

 

[nebon]

the same thing happen

still the same error ...
(Me.SHOW_ID.Value is a reference to a combobox value (but this is the right way of doing it , I hope))
Do you think there is another way of doing it ?

 

[Fizzio]

On looking again at your code, you do not actually have any code that adds a new record, just checks a solitary value. if this is the case, have you considered using the DLookup function instead?

Also, you do not need to use the .value on the end of me.Show_ID

in query1, do you have some criteria that filters that recordset?

 

[nebon]

hallo again (thnx alot for the help btw).
well , the thing is , I've just added 2 diffrent msgs in the if - else statment just to check if its working. but it would not get that far, it would stop at the :

rst.Open _
Source:="SELECT query1.status" & _
" FROM query1" & _
" WHERE (query1.show_id) = " & intShow_ID, _
Options:=adCmdText

I think vb doesnt like me having the ) = " & intShow_ID, _ at the end of the 4th line ... I mean If I delete the WHERE line , this would work but it would display (of course) the first record in the query which doesnt meet my criteria !

DLookup function ??

hmm how would I use that in my case ?
(thnx again)

 

[Fizzio]

If you are only looking up one value, DLookup should suffice
rather than

Dim rst As ADODB.Recordset, intShow_ID as integer

Set rst = New ADODB.Recordset 
rst.ActiveConnection = CurrentProject.Connection 
rst.Properties("Jet OLEDB:Inconsistent") = True 
intShow_ID = Me.SHOW_ID.Value

rst.Open _ 
Source:="SELECT query1.status" & _ 
" FROM query1" & _ 
" WHERE (query1.show_id) = " & intShow_ID, _ 
Options:=adCmdText 

If rst!Status = 0 Then

try

if DLookup ("[status]","query1", "[show_id] = " & me.SHOW_ID) = 0 then

 

[nebon]

I think I've annoyed you to much already !
the good news is the error msg changes, the bad news is , still giving me error . now its telling me "you cancelled the previous operation" (am I the only one getting these funny error msgs?) thnx again

 

[Fizzio]

On what event is your code under?

 

[nebon]

under Onclick Event !

 

[Fizzio]

I just read the first post (and that answer was there - oops)

You do not need the cancel=true event, as this is reserved for before_xxxxx events. If you want to halt the running of the event, use

Exit Sub

 

[nebon]

hmm , I appreciate your help, but this seems that this doesnt want to work !??? I am really pissed of man , this is suppose to be something really easy !! grab the value from a field in a accordance to the criteria then if 0 then success else NOO!
this just doesnt want to work !

Thanks alot anyways! )

 

[PaddyIrishMan]

Can you post a compacted example of your database?

 

[nebon]

Thanks alot for the effort everyone ..!

well for whom ever is intressted in my problem here is the link for the zipped .mdb file ....


the form name is booking , if yuo try to press add booking , see what happens !! the same error over and over again always !
I have tried my method fizzios method (which should definitely work, but it doesnt )

http://www.chuchu.plus.com\db2002.zip

thanks in advance!

 

[PaddyIrishMan]

Hi,
The download is access Xp - could you save it as an earlier version & repost?
(Access97 & 2k wont open it)

 

[nebon]

sure here is the 2000 converted version (thanx alot) :
www.chuchu.plus.com\db.zip

 

[PaddyIrishMan]

The problem with your DLookup is that you need to pass in apostrophes around the paramater you are passing in to it.
Try:

Dim vTest As Variant
    vTest = DLookup("status", "query1", "[show_id] = " & "'" & Me.SHOW_ID & "'")

    MsgBox vTest

Now the DLookup works correctly.
Does this fix all of your problems?

 

[nebon]

First of all , Thanks alot man, it worked ?? I used 2 diffrent methods none would work ... and I knew there was something ridiculous behind the error .... anyway thanks alot .. but to be honest, it doesnt fix all my problem ....

if you take a look at the booking for , the whole booking form is a big mess.

the question , I am trying to create a booking where the user only needs to select the Film_title , then the query should automatically locate the appropriate shows and list them in a list with show_date and Show_Time , I have tried to do this by having a combobox where you have film_ref and film_title (tblfilm) where film_ref is hidden in the combobox , then I made another combobox for the show_date show_time and also film_ref as hidden .. and as criteria for that field , I had :
me!combo0.column(0)
but it didnt like me having this criteria .... do you have any idea of how this could be solved ?

 

[PaddyIrishMan]

Hi Nebon,
I'll be a bit busy with work for about the next hour, I'll take a look then & do up an example of some sort for you.

Regards,
Patrick

 

[nebon]

oki , thanks alot man ... really appreciate it !

 

[PaddyIrishMan]

Hi Nebon,
Firstly:

"'" & YourTest & "'"

Concatenates apsostrophes into the text string i.e. YourTest
becomes 'YourTest'
When passing string values in SQL they should be surrounded by apostrophes.

Secondly:
I've posted a sample database which I downloaded from this forum some time ago.
Can't remember who posted it but I think it was jfgambit..
Anyway it is a good example of how subforms are used - you should check this out as this is the way to go if you want to improve your db.

HTH,
Patrick