simple stuff really...

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
MrdogkickAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

quiklearnerCommented:
no code included..
0
MrdogkickAuthor Commented:
oops
0
MrdogkickAuthor Commented:
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
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

quiklearnerCommented:
what type of object is dbengine.  what type of object does the opendatabase method of the dbengine return?
0
MrdogkickAuthor Commented:
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
quiklearnerCommented:
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
MrdogkickAuthor Commented:
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
quiklearnerCommented:
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
MrdogkickAuthor Commented:
what was you?

Which lines should the code you provided replace??
0
quiklearnerCommented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
MrdogkickAuthor Commented:
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
quiklearnerCommented:
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
MrdogkickAuthor Commented:
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
quiklearnerCommented:
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
MrdogkickAuthor Commented:
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
quiklearnerCommented:
what error, where?
0
MrdogkickAuthor Commented:
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
quiklearnerCommented:
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
MrdogkickAuthor Commented:
just out of interest, what does that last line mean with the 2,4??
0
MrdogkickAuthor Commented:
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
quiklearnerCommented:
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
MrdogkickAuthor Commented:
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
quiklearnerCommented:
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
quiklearnerCommented:
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Visual Basic Classic

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.