Solved

simple stuff really...

Posted on 2007-04-11
24
201 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
Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

 
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
 

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

ScreenConnect 6.0 Free Trial

At ScreenConnect, partner feedback doesn't fall on deaf ears. We collected partner suggestions off of their virtual wish list and transformed them into one game-changing release: ScreenConnect 6.0. Explore all of the extras and enhancements for yourself!

Question has a verified solution.

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

Suggested Solutions

The debugging module of the VB 6 IDE can be accessed by way of the Debug menu item. That menu item can normally be found in the IDE's main menu line as shown in this picture.   There is also a companion Debug Toolbar that looks like the followin…
Background What I'm presenting in this article is the result of 2 conditions in my work area: We have a SQL Server production environment but no development or test environment; andWe have an MS Access front end using tables in SQL Server but we a…
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
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…

773 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