-
Compile Error VB6 Code.. HELP
Hi, im working through a guide on using sql and parameters in VB6, im using the BIBLIO.MDB that come with vb6. Ive been orking through the code in the manual but when i enter the follwing bit it comes up with error "complile error expected end of statement", ive gone through everything and it all looks exctly as it should according to the instructions. Id be reallky gratefull if anyone coulf have a quick look and point out any mistakes.
Code:
Sub runQuery()
Dim dbf As Database, rectemp As Recordset
Dim qdftemp As QueryDef
Dim strSQL As String
Dim blnfoundquery As Boolean
On Error GoTo queryerror
Set dbftemp = Workspaces(0).OpenDatabase(bibio_path & _
"\biblio.mdb")
For Each qdftemp In dbftemp.QueryDefs
If qdftemp.Name = "publishers titles by author" Then
blnfoundquery = True
Exit For
End If
Next
If blnfoundquery = False Then
strSQL = "PARAMETERS pintPubID Long; " & _
"SELECT Authors.Author, Titles.Title, " & _
"titles.ISBN, " & _
"Titles.[year published], Publishers.name " & _
"From (publishers inner join titles on "
strSQL = strSQL & "publishers.PubID = " _
"Titles.PubID) " & _
"INNER JOIN " & _
"(Authors INNER JOIN [Title Author] ON " & _
"Authors.au_ID = [Title Author].Au.ID) on " & _
"Titles.ISBN = [Title Author].ISBN WHERE " & _
"publishers.pubID " & _
"= pintPubID ORDER by Authors.Author;"
Set qdbftemp = dbftemp.CreateQueryDef( _
"publisher's titles by " & _
"author", strSQL)
Else
Set qdftemp = dbftemp.QueryDefs( _
"publisher's titles by author")
End If
qdftemp.Parameters![pintpubid] = selectedpubid
Set dtadata.Recordset = gdftemp.OpenRecordset()
If dtadata.Recordset.RecordCount > 0 Then
frmmain.Caption = "chapter 5.7 example - " & _
Str$(dtadata.Recordset.RecordCount) & _
IIf(dtadata.Recordset.RecordCount = 1, _
" Title", " Titles")
Else
frmmain.Caption = frmmain.Caption = "chapter 5.7 _ example - no titles"
End If
On Error GoTo 0
Exit Sub
queryerror:
MsgBox Err.Description, vbExclamation
Exit Sub
End Sub
The erro comes up at lines
Code:
strSQL = strSQL & "publishers.PubID = " _
"Titles.PubID) " & _
"INNER JOIN " & _
"(Authors INNER JOIN [Title Author] ON " & _
"Authors.au_ID = [Title Author].Au.ID) on " & _
"Titles.ISBN = [Title Author].ISBN WHERE " & _
"publishers.pubID " & _
"= pintPubID ORDER by Authors.Author;"
Thanks for your time
-
Re: Compile Error VB6 Code.. HELP
I think it is just a missing line continuation character...
VB Code:
strSQL = strSQL & "publishers.PubID = " [B][COLOR=Red]&[/COLOR][/B] _
"Titles.PubID) " & _
"INNER JOIN " & _
"(Authors INNER JOIN [Title Author] ON " & _
"Authors.au_ID = [Title Author].Au.ID) on " & _
"Titles.ISBN = [Title Author].ISBN WHERE " & _
"publishers.pubID " & _
"= pintPubID ORDER by Authors.Author;"
-
Re: Compile Error VB6 Code.. HELP
quality, thank you, ive got another problem, iv got a second form which ive used the code below as the guide insturucted but whe n i try to run it it say "compile error, variable not definedW, ive highlighted the line the error coems up at. looking at it the instructions said to only use the full option explicit staement on the frmmain on the frmselectpublisher its says to simply use option explicit, could this be wher the problem is??
Code:
Option Explicit
Private Sub Form_Load()
Dim dbftemp As Database, rectemp As Recordset
Dim strSQL As String
On Error GoTo loaderror
Set dbftemp = Workspaces(0).OpenDatabase(BIBLIO_PATH)
strSQL = "SELECT [PUBID], [company name] from " & _
"[publishers]"
Set rectemp = dbftemp.OpenRecordset(strSQL)
If rectemp.RecordCount Then
rectemp.MoveFirst
Do Until rectemp.EOF
If Not IsNull(rectemp![company name]) Then
LstPublishers.AddItem rectemp![company name]
Else
LstPublishers.AddItem ""
End If
LstPublishers.ItemData(LstPublishers.NewIndex) = _
rectemp![PubID]
rectemp.MoveNext
Loop
Else
MsgBox "there are no publishers in the database.", _
vbCritical
End
End If
On Error GoTo 0
Exit Sub
loaderror:
MsgBox Err.Description, vbCritical
End
End Sub
there line concerned is:
Code:
Set dbftemp = Workspaces(0).OpenDatabase(BIBLIO_PATH)
thanks again for your time,
-
Re: Compile Error VB6 Code.. HELP
Yes, with Option Explicit turned on (as should always be), your variable dbfTemp is only valid in the Form Load event of whatever the form is. If you need this variable across forms, place its declaration in a module.
-
Re: Compile Error VB6 Code.. HELP
ive just tried the code as shown below but i get an eror saying the db is already open,
Code:
Option Explicit
Const BIBLIO_PATH = "C:\program files\microsoft visual studio\vb98"
Public selectedpubid As Integer
Private Sub Form_Load()
Dim dbftemp As Database, rectemp As Recordset
Dim strSQL As String
On Error GoTo loaderror
Set dbftemp = Workspaces(0).OpenDatabase(BIBLIO_PATH)
strSQL = "SELECT [PUBID], [company name] from " & _
"[publishers]"
Set rectemp = dbftemp.OpenRecordset(strSQL)
If rectemp.RecordCount Then
rectemp.MoveFirst
Do Until rectemp.EOF
If Not IsNull(rectemp![company name]) Then
LstPublishers.AddItem rectemp![company name]
Else
LstPublishers.AddItem ""
End If
LstPublishers.ItemData(LstPublishers.NewIndex) = _
rectemp![PubID]
rectemp.MoveNext
Loop
Else
MsgBox "there are no publishers in the database.", _
vbCritical
End
End If
On Error GoTo 0
Exit Sub
loaderror:
MsgBox Err.Description, vbCritical
End
-
Re: Compile Error VB6 Code.. HELP
That is probably because before your code closed it, it blew up on the variable not being defined, so there was no graceful closure.
If you can find the database running in Task Manager, end it, and then run your code again.
-
Re: Compile Error VB6 Code.. HELP
Thing is im working from a tutorial guide which is obviously wrong, ive followed the instructions exactly but they dont work, is there any chance i could post the 2 forms and someone could point me to what needs to be changed as i really am stumped?
-
Re: Compile Error VB6 Code.. HELP
Quote:
Originally Posted by ricodstar
Thing is im working from a tutorial guide which is obviously wrong, ive followed the instructions exactly but they dont work, is there any chance i could post the 2 forms and someone could point me to what needs to be changed as i really am stumped?
Sure, go ahead (and not that it will make any difference, but yours is not the first tutorial guide to send someone just starting out down the garden path of confusion.)
-
1 Attachment(s)
Re: Compile Error VB6 Code.. HELP
Ok, in the attached folder is the forms as creted follwing the instructions,
cheers
sean
-
Re: Compile Error VB6 Code.. HELP
Before I get too carried away and rewrite the whole thing, is the purpose of this little project just an excerise in database connectivity? I'm afraid if I rewrite everything then you will be lost for your next chapter.
PS: One thing I would do now, regardless of what the books says, is to add a module to your project (that was my first step). Take your database and recordset objects, move them to the module, and make them public. Also, the Const for BIBLIO_MDB should be moved to the module and made public.
-
Re: Compile Error VB6 Code.. HELP
Im using it just to get an idea of the way to do queries from vb, theer isnt another chapter after this so any changes you can make would be greatly appriciated, then i can look it over and get to grips with it from there, its a bit of a nightmare trying to learn from code thats faulty.
-
Re: Compile Error VB6 Code.. HELP
Don't give up the ship.
I haven't worked with data controls or DAO in quite a few years, so I'm having to look up stuff I used to know by heart. I'd like to keep this as close to your book's example as possible.
-
Re: Compile Error VB6 Code.. HELP
thankyou so much for doing this, your an absolute lifesaver :)
-
Re: Compile Error VB6 Code.. HELP
I had an MDAC issue combined with using DAO against what turned out to be an Access 97 database, but I think I got those corrected. Give this a shot a see if it works for you.
Also, I would strongly suggest moving from DAO, regardless of what the books you have are telling you, to ADO. In the 'real' world, that is what you are going to be using in all likelyhood.
If this doesn't work on your machine, let me know.
-
Re: Compile Error VB6 Code.. HELP
Hi, ive just tried it and the frmselect publishers comes up fine but when i select a publisher then the ok it comes up withy error "run time 3061, to few parameters expected 1"???
-
Re: Compile Error VB6 Code.. HELP
sorry shiuld have added the line the error comes up on
Code:
Set rectemp = dbftemp.OpenRecordset(strSQL, dbOpenSnapshot, dbSQLPassThrough)
-
Re: Compile Error VB6 Code.. HELP
Ok. I stuipdly upgraded my copy of biblio to 2000 (which is when I ran into the MDAC problems using DAO. Since you are still using an Access 97 copy, then you probably don't need to make is a passthrough query. Play around with that line, and see if you can get it to work. I no longer have an Access 97 copy of biblio...
-
Re: Compile Error VB6 Code.. HELP
im using access 2002-2003, the file biblio is in 97 format but i can convert it to both 200 and 2003, ive just tried both and it says "unrecognized database format" for both
-
Re: Compile Error VB6 Code.. HELP
Quote:
Originally Posted by ricodstar
im using access 2002-2003, the file biblio is in 97 format but i can convert it to both 200 and 2003, ive just tried both and it says "unrecognized database format" for both
Don't convert it....leave it as a 97 format. Converting it is what messed me up.
-
Re: Compile Error VB6 Code.. HELP
ok i will do, would learning to use ado be a whole lot better for me, im really new to vb and although i was pritty good at access i dont know wheer to start with vb and its quite frustrating, you said " Play around with that line, and see if you can get it to work. I no longer have an Access 97 copy of biblio", im sorry to seem dumb but by play around what do you mean??
sorry
-
Re: Compile Error VB6 Code.. HELP
Quote:
Originally Posted by ricodstar
ok i will do, would learning to use ado be a whole lot better for me, im really new to vb and although i was pritty good at access i dont know wheer to start with vb and its quite frustrating, you said " Play around with that line, and see if you can get it to work. I no longer have an Access 97 copy of biblio", im sorry to seem dumb but by play around what do you mean??
sorry
A: You are NOT dumb and no one thinks you are.
B: Yes, it would be much better to learn ADO.
C: I meant play around with
Code:
Set rectemp = dbftemp.OpenRecordset(strSQL, dbOpenSnapshot, dbSQLPassThrough)
Only the first parameter is required. The other two are optional, so see what happens if you remove one or both.
There are some pretty decent ADO Tutorials out there, and a lot of ADO examples on this site.
-
Re: Compile Error VB6 Code.. HELP
Here's a couple of ADO tuts I've written. Ignore the fact that it still says SQL Server in the description. It's really all Access & ADO.
http://www.developerkb.com/modules/w...hp?category=23
And there are plenty more out there too.
Tg
-
Re: Compile Error VB6 Code.. HELP
thankyou both loads, im gonna give those tuts you offered a go, see if i can get somewhere 2moro, I tried variations on that line in the code and got no where so maybe ados best, think im gonna take a break tho...starting to pull my hair out!!!!
-
Re: Compile Error VB6 Code.. HELP
Change dbOpenSnapshot to dbOpenDynaset and see if that helps.
-
Re: Compile Error VB6 Code.. HELP
no still says to few parameters, ive tried as many combinatiosn as poss.. no worries 2moro im gonna work through the tut recommended so hopefully ill get somewhere with that... thanks for all your help
-
Re: Compile Error VB6 Code.. HELP
if you do think of anything, let me know and ill give it a try :rolleyes:
-
Re: Compile Error VB6 Code.. HELP
It's a typo in runQuery():
VB Code:
strSQL = "SELECT Authors.Author, Titles.Title, "
strSQL = strSQL & "titles.ISBN, "
strSQL = strSQL & "Titles.[year published], Publishers.name "
strSQL = strSQL & "From (publishers inner join titles on "
strSQL = strSQL & "publishers.PubID = "
strSQL = strSQL & "Titles.PubID) "
strSQL = strSQL & "INNER JOIN "
strSQL = strSQL & "(Authors INNER JOIN [Title Author] ON "
strSQL = strSQL & "Authors.au_ID = [Title Author].Au[COLOR=Red]_[/COLOR]ID) on " '<---- ON THIS LINE
strSQL = strSQL & "Titles.ISBN = [Title Author].ISBN WHERE "
strSQL = strSQL & "publishers.pubID = " & selectedpubid
strSQL = strSQL & " ORDER by Authors.Author"