View record it gives error

[ranesmitas]

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(clien t_no,5)) as client_no from client_master"
Set rs = conn.Execute(sq l)
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.Enable d = True
Command4.Enable d = True
rs.Open "select * from client_master", conn, adOpenDynamic, adLockOptimisti c
'sql = "select * from client_master"
'Set rs = conn.Execute(sq l)

Call viewrecord
Command8.Enable d = True
Command9.Enable d = True
Command10.Enabl ed = True
Command11.Enabl ed = True

End Sub

Plese tell me how to do

[debasisdas]

What is in viewrecord ?

[ranesmitas]

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

[QVeen72]

Hi,

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

Regards
Veena

[debasisdas]

Are you able to add records to database by the code Command1_Click( )

[ranesmitas]

yes,I add record command 1_click

[ranesmitas]

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 createconnectio n() and i call it in that form .function is

Public Function createconnectio n()
Set conn = New ADODB.Connectio n

conn.Open "Provider=SQLOL EDB;Data source=(local); database=produc t;user id =sa;password="
Set rs1 = New ADODB.Recordset

msql = "select * from sysobjects where xtype='U' and name='product_m aster'"
Set rs1 = conn.Execute(ms ql)
If rs1.EOF Or BOF = True Then
msql = "create table product_master( product_no varchar(6) primary key,description varchar(25),uni t_measure varchar(10),qty _on_hand int,reorder_lvl int,cost_price float,selling_p rice float)"
conn.Execute (msql)
msql = "create table client_master(c lient_no varchar(6) primary key,name varchar(20),add ress varchar(30),cit y varchar(15),sta te varchar(20),pin code int,bal_due float)"
conn.Execute (msql)
msql = "create table salesman_master (salesman_no varchar(6)prima ry key,name varchar(20),add ress varchar(20),rem ark varchar(50))"
conn.Execute (msql)
msql = "create table sales_order(s_o rder_no varchar(6) primary key,s_order_dat e datetime,client _no varchar(6) constraint fkcn foreign key references client_master(c lient_no),dely_ addr varchar(30),sal esman_no varchar(6) constraint fksm foreign key references salesman_master (salesman_no),s tatus varchar(20),del y_type char(1),dely_da te datetime)"
conn.Execute (msql)
msql = "create table sales_order_det ails(s_order_no varchar(6) constraint fkso foreign key references sales_order(s_o rder_no),produc t_no varchar(6),prod uct_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
.ActiveConnecti on = conn
'.CursorType = adOpenDynamic
.CursorType = adOpenStatic
.LockType = adLockOptimisti c
.CursorLocation = adUseClient
.Source = "select * from sales_order"
.Open
End With

End Function