By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,039 Members | 1,824 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,039 IT Pros & Developers. It's quick & easy.

View record it gives error

P: 21
Hi ,I am using Visual Basic Backend SQL
I create form in which i create Command button
NEW ,MODIFY, VIEW,SAVE,EXIT

all my programme is running but when i add new record and save and then
i press VIEW button it gives error
i.e Object VAriable or with block variable not set
my code is

Private Sub Command1_Click() ' Add New Record
sql = "select max(right(client_no,5)) as client_no from client_master"
Set rs = conn.Execute(sql)
mn = rs("client_no")
If rs("client_no") <> "" Then
nc = Format(mn + 1, "00000")
Else
nc = "00001"
End If
Text1.Text = "C" & nc
Text2.Text = ""
Text3.Text = ""
Text4.Text = ""
Text5.Text = ""
Text6.Text = ""
Combo1.Text = ""
Text2.SetFocus
mmode = "save"
end sub

Private Sub Command3_Click()
rs.Close
Text1.Enabled = True
Text2.Enabled = True
Text3.Enabled = True
Text4.Enabled = True
Text5.Enabled = True
Text6.Enabled = True
Combo1.Enabled = True
Command2.Enabled = True
Command4.Enabled = True
rs.Open "select * from client_master", conn, adOpenDynamic, adLockOptimistic
'sql = "select * from client_master"
'Set rs = conn.Execute(sql)

Call viewrecord
Command8.Enabled = True
Command9.Enabled = True
Command10.Enabled = True
Command11.Enabled = True

End Sub

Plese tell me how to do
Jun 10 '08 #1
Share this Question
Share on Google+
6 Replies


debasisdas
Expert 5K+
P: 8,127
What is in viewrecord ?
Jun 10 '08 #2

P: 21
viewrecord is procedure i.e

Private Sub viewrecord()
If rs("client_no") <> "" Then
Text1.Text = rs("client_no")
Else
Text1.Text = ""
End If

If rs("name") <> "" Then
Text2.Text = rs("name")
Else
Text2.Text = ""
End If

If rs("address") <> "" Then
Text3.Text = rs("address")
Else
Text3.Text = ""
End If

If rs("city") <> "" Then
Text4.Text = rs("city")
Else
Text4.Text = ""
End If

If rs("pincode") <> "" Then
Text5.Text = rs("pincode")
Else
Text5.Text = ""
End If

If rs("bal_due") <> "" Then
Text6.Text = rs("bal_due")
Else
Text6.Text = ""
End If

If rs("state") <> "" Then
Combo1.Text = rs("state")
Else
Combo1.Text = ""
End If


End Sub
Jun 11 '08 #3

QVeen72
Expert 100+
P: 1,445
Hi,

Check these:
Declare RS and Conn Objects Form Level..
Open Connection Object in form Load..

Regards
Veena
Jun 11 '08 #4

debasisdas
Expert 5K+
P: 8,127
Are you able to add records to database by the code Command1_Click()
Jun 11 '08 #5

P: 21
yes,I add record command 1_click
Jun 12 '08 #6

P: 21
Hi,

Check these:
Declare RS and Conn Objects Form Level..
Open Connection Object in form Load..

Regards
Veena
i create module in that i create function i.e createconnection() and i call it in that form .function is

Public Function createconnection()
Set conn = New ADODB.Connection

conn.Open "Provider=SQLOLEDB;Data source=(local);database=product;user id =sa;password="
Set rs1 = New ADODB.Recordset

msql = "select * from sysobjects where xtype='U' and name='product_master'"
Set rs1 = conn.Execute(msql)
If rs1.EOF Or BOF = True Then
msql = "create table product_master(product_no varchar(6) primary key,description varchar(25),unit_measure varchar(10),qty_on_hand int,reorder_lvl int,cost_price float,selling_price float)"
conn.Execute (msql)
msql = "create table client_master(client_no varchar(6) primary key,name varchar(20),address varchar(30),city varchar(15),state varchar(20),pincode int,bal_due float)"
conn.Execute (msql)
msql = "create table salesman_master(salesman_no varchar(6)primary key,name varchar(20),address varchar(20),remark varchar(50))"
conn.Execute (msql)
msql = "create table sales_order(s_order_no varchar(6) primary key,s_order_date datetime,client_no varchar(6) constraint fkcn foreign key references client_master(client_no),dely_addr varchar(30),salesman_no varchar(6) constraint fksm foreign key references salesman_master(salesman_no),status varchar(20),dely_type char(1),dely_date datetime)"
conn.Execute (msql)
msql = "create table sales_order_details(s_order_no varchar(6) constraint fkso foreign key references sales_order(s_order_no),product_no varchar(6),product_rate int,qty_ord int,qty_disp int,constraint fkpn foreign key (product_no) references product_master(product_no))"
conn.Execute (msql)


Else
'MsgBox "table already exist"
End If
Set rs = New ADODB.Recordset
With rs
.ActiveConnection = conn
'.CursorType = adOpenDynamic
.CursorType = adOpenStatic
.LockType = adLockOptimistic
.CursorLocation = adUseClient
.Source = "select * from sales_order"
.Open
End With

End Function
Jun 12 '08 #7

Post your reply

Sign in to post your reply or Sign up for a free account.