[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

simple stuff really...

Posted on 2007-04-11
24
Medium Priority
?
235 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
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
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 2000 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
 

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 does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

When trying to find the cause of a problem in VBA or VB6 it's often valuable to know what procedures were executed prior to the error. You can use the Call Stack for that but it is often inadequate because it may show procedures you aren't intereste…
I was working on a PowerPoint add-in the other day and a client asked me "can you implement a feature which processes a chart when it's pasted into a slide from another deck?". It got me wondering how to hook into built-in ribbon events in Office.
Get people started with the process of using Access VBA to control Outlook using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Microsoft Outlook. Using automation, an Access applic…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
Suggested Courses
Course of the Month17 days, 16 hours left to enroll

831 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