Solved

simple stuff really...

Posted on 2007-04-11
24
193 Views
Last Modified: 2010-04-30
Hello all, I am developing an appointment booking system in vb and have have just finished the code for the add new customer page but there is an error (highlighted with **'s) could you please have a look at my code and let me know why this is??? Cheers
0
Comment
Question by:Mrdogkick
  • 12
  • 12
24 Comments
 
LVL 4

Expert Comment

by:quiklearner
ID: 18891734
no code included..
0
 

Author Comment

by:Mrdogkick
ID: 18891740
oops
0
 

Author Comment

by:Mrdogkick
ID: 18891751
Dim db As Database
Dim rs As Recordset
Dim WS As Workspace
Dim Max As Long

Set WS = DBEngine.Workspaces(0)
    DbFile = (App.Path & "\Database\Salon45.mdb")
    PwdString = ""
*******Set db = DBEngine.OpenDatabase(DbFile, False, False, ";PWD=" & PwdString)*********
Set rs = db.OpenRecordset("Customer", dbOpenTable)
If txtname.Text = rs.Index Then
MsgBox "This contact already exists !", vbInformation, "Warning"
Else
rs.AddNew
rs("custName") = txtFName.Text
rs("custSName") = txtSName.Text
rs("custDOB") = txtDOB.Text
rs("custAdd") = txtAdd.Text
rs("custTel") = txtHome.Text
rs("custMob") = txtMob.Text
rs("custEmail") = txtEmail.Text
rs("custMedHis") = txtMedHis.Text
rs.Update
Max = rs.RecordCount
frmMain.lblTotalContacts.Caption = Max
rs.MoveFirst

frmMain.List1.Clear

For i = 1 To Max
    frmMain.List1.AddItem rs!FullName
    rs.MoveNext
Next i
Unload Me
End If
End If
End Sub
0
 
LVL 4

Expert Comment

by:quiklearner
ID: 18891779
what type of object is dbengine.  what type of object does the opendatabase method of the dbengine return?
0
 

Author Comment

by:Mrdogkick
ID: 18891801
I'm not sure what you mean...Im using an Access database if that helps? Ive upped the points as 50 seems kinda crap.
0
 
LVL 4

Expert Comment

by:quiklearner
ID: 18891853
not a big deal..  I am saying the order and contents of your string is de[pendent on the object type of dbengine.  Somewhere dbengine was dimmed: Dim dbengine as XXXXX  or if it is as object then it was set somewhere set dbengine = XXXX  or it is possiby a control on a form.. if so what kind of object or control is it?
0
 

Author Comment

by:Mrdogkick
ID: 18891875
I haven't put a control on, someone told me it was unneccesary...To be honest I have taken this code from some free source code and modified it to my program and db... Is this even a good way of storing the data to the db?
0
 
LVL 4

Expert Comment

by:quiklearner
ID: 18891970
that was me i believe:
do this instead:
Dim DBEngine as object
set DBEngine = createobject("ADODB.Connection")
DBEngine.Open "DRIVER={Microsoft Access Driver (*.mdb)};DBQ=" & DbFile & ";UID=;PWD="
0
 

Author Comment

by:Mrdogkick
ID: 18892038
what was you?

Which lines should the code you provided replace??
0
 
LVL 4

Accepted Solution

by:
quiklearner earned 500 total points
ID: 18892116
because dbengine does exist and i dont know what the object type is replace dbengine with odb in my last response and replace just the bad line..  yoiu may need to change subsequent dbengine to odb though..
0
 

Author Comment

by:Mrdogkick
ID: 18892170
so, change:

Dim DBEngine as object
set DBEngine = createobject("ADODB.Connection")
DBEngine.Open "DRIVER={Microsoft Access Driver (*.mdb)};DBQ=" & DbFile & ";UID=;PWD="

to:

Dim odb as object
set odb = createobject("ADODB.Connection")
odb.Open "DRIVER={Microsoft Access Driver (*.mdb)};DBQ=" & DbFile & ";UID=;PWD="

and put it all in where the bad line was???
0
 
LVL 4

Expert Comment

by:quiklearner
ID: 18892245
yes.  then you may need to change some of the lines below that use dbengine (whatever that is) and replace it with odb, but i would at least get this working first..  I would be more confortable if you  then set rs = odb.execute("SELECT * FROM Customer")
but to have this work you may need to make rs as Object in the dim at the top.  If this is actually someone elses code may i ask, what exactly is the goal of querying the customer table?  are you loading a specific customer? or all of them?
0
How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

 

Author Comment

by:Mrdogkick
ID: 18892313
it is someone elses code but they were doing an address book and this was on the create new contact page. Customers is a table in my db and this code is there to hopefully store the customer's details in the table, not to load them up. Have I done something wrong??
0
 
LVL 4

Expert Comment

by:quiklearner
ID: 18892363
nope just misunderstood what you were doing..  If you just replace the bad line and the dbengine with odb on the line after the bad one you should be pretty close..
0
 

Author Comment

by:Mrdogkick
ID: 18892465
ok i replaced the line and:

1) replaced
Set rs = db.OpenRecordset("Customer", dbOpenTable)

with

Set rs = odb.Execute("SELECT * FROM Customer")

2) replaced
Dim rs As Recordset

with

Dim rs as object

its still giving me errors, what have I done wrong?
0
 
LVL 4

Expert Comment

by:quiklearner
ID: 18892484
what error, where?
0
 

Author Comment

by:Mrdogkick
ID: 18892574
Error is now where the stars are and it says 'current recordset does not support updating. this may be a limitation of the provider, or of the selected locktype.


Private Sub cmdNew_Click()
If txtCustId.Text = vbNullString Then
txtCustId.Text = ""
End If
If txtSName.Text = vbNullString Then
txtSName.Text = ""
End If
If txtFName.Text = vbNullString Then
txtFName.Text = ""
End If
If txtDOB.Text = vbNullString Then
txtDOB.Text = ""
End If
If txtAdd.Text = vbNullString Then
txtAdd.Text = ""
End If
If txtHome.Text = vbNullString Then
txtHome.Text = ""
End If
If txtMob.Text = vbNullString Then
txtMob.Text = ""
End If
If txtEmail.Text = vbNullString Then
txtEmail.Text = ""
End If
If txtEmail.Text = vbNullString Then
txtEmail.Text = ""
End If
If txtMedHis.Text = vbNullString Then
txtMedHis.Text = ""
MsgBox "You must fill in all the text boxes!", vbExclamation, "Warning"

Exit Sub
Else
Dim db As Database

Dim WS As Workspace
Dim Max As Long
Dim rs As Object
Set WS = DBEngine.Workspaces(0)
    DbFile = (App.Path & "\Database\Salon45.mdb")
    PwdString = ""
Dim odb As Object
Set odb = CreateObject("ADODB.Connection")
odb.Open "DRIVER={Microsoft Access Driver (*.mdb)};DBQ=" & DbFile & ";UID=;PWD="
Set rs = odb.Execute("SELECT * FROM Customer")
If txtCustId.Text = rs.Index Then
MsgBox "This contact already exists !", vbInformation, "Warning"
Else
*****rs.AddNew*****
rs("custName") = txtFName.Text
rs("custSName") = txtSName.Text
rs("custDOB") = txtDOB.Text
rs("custAdd") = txtAdd.Text
rs("custTel") = txtHome.Text
rs("custMob") = txtMob.Text
rs("custEmail") = txtEmail.Text
rs("custMedHis") = txtMedHis.Text
rs.Update
Max = rs.RecordCount
frmMain.lblTotalContacts.Caption = Max
rs.MoveFirst

frmMain.List1.Clear

For i = 1 To Max
    frmMain.List1.AddItem rs!FullName
    rs.MoveNext
Next i
Unload Me
End If
End If
End Sub
0
 
LVL 4

Expert Comment

by:quiklearner
ID: 18892643
replace the set rs =.....   statement with the following two lines:
set rs = createobject("ADODB.Recordset")
rs.Open "Customer", odb,2,4

sorry i gave you the line you used before i realized you were adding data not getting it..  thats why you got the error.
0
 

Author Comment

by:Mrdogkick
ID: 18892659
just out of interest, what does that last line mean with the 2,4??
0
 

Author Comment

by:Mrdogkick
ID: 18892716
Well, there are no more errors now when i tried adding a record which is good, but the bad news is nothing was saved in the db. I'm calling it a day, cheers for all the help today, I'll be back tomorrow with plenty more annoying questions with stupidly high points and grades if you're at all interested?
0
 
LVL 4

Expert Comment

by:quiklearner
ID: 18892719
2 is open dynamic (static by default which makes it read only) 4 to lock optmistic, also related to how records are locked when editing.  Just curious, is this vba like inside of Access or something?  Also, have you added any references to Microsoft ActiveX Data Objects?
0
 

Author Comment

by:Mrdogkick
ID: 18892741
a)no its not inside of access, why does it seem like that??

b)I dont think I have referenced any active x objects, should I?

0
 
LVL 4

Expert Comment

by:quiklearner
ID: 18892764
it dont really matter, we are tying to the object by full name ADODB.Class anyway....  It may make some things easier though..  If you hit f2 and search for recordset, what library does it show up under?  If your declarations of "recordset" wern't failing before, you obviously are already using some database related libraries already..
0
 
LVL 4

Expert Comment

by:quiklearner
ID: 18892780
I was wondering what the worksheet object was that i saw...  it looks like vba out of an office product more so than standalone vb..
0

Featured Post

What Security Threats Are You Missing?

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

I’ve seen a number of people looking for examples of how to access web services from VB6.  I’ve been using a test harness I built in VB6 (using many resources I found online) that I use for small projects to work out how to communicate with web serv…
Most everyone who has done any programming in VB6 knows that you can do something in code like Debug.Print MyVar and that when the program runs from the IDE, the value of MyVar will be displayed in the Immediate Window. Less well known is Debug.Asse…
Get people started with the process of using Access VBA to control Excel using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Excel. Using automation, an Access application can laun…
This lesson covers basic error handling code in Microsoft Excel using VBA. This is the first lesson in a 3-part series that uses code to loop through an Excel spreadsheet in VBA and then fix errors, taking advantage of error handling code. This l…

708 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now