Visual Basic 6 help--add button and flexgrid

[panget]

I am supposed to create a form with a flexgrid that should display all contents of my database
books.mdb. I also added a button called "Add" which when clicked should display another form with
textboxes where I could fill up the contents I want to add in my database. When I click the button,
this second form should disappear while the main form should still display all contents of the database including the new entry I've added.

Now the problem is the add button doesn't work. I have opened the components and references needed for this project to work. Is there a problem with my code? Please help. Thank you.


================Form1===================

Option Explicit
 
Private rscars As New ADODB.Recordset
 
Public n1 As String
Public n2 As String
Public n3 As String
Public n4 As String
Public n5 As String
 
Private Sub form_load()
Dim strqry As String
 
Call opencars
 
strqry = "Select * from books"
Set rscars = retrieve(rscars, strqry)
Set MSHFlexGrid1.DataSource = rscars.DataSource
With MSHFlexGrid1
.ColWidth(0) = 1000
.ColWidth(1) = 1500
.ColWidth(2) = 3000
.ColWidth(3) = 2000
.ColWidth(4) = 4000
.ColWidth(5) = 2000
End With
 
 
End Sub
 
Private Sub Command1_Click()
Dim strqr As String
Form2.Show vbModal
 
strqr = "insert into books (cat, desc, price, cont, store)" _
& "values('" & n1 & "','" & n2 & "','" & n3 & "','" & n4 & "','" & n5 & "')"
cn.execute strqr
rscars.Requery
Set rscars.DataSource = retrieve(rscars, strqr)
Call Display
End Sub
 
Private Sub Display()
With rscars
If .RecordCount < 1 Then
MsgBox "Table is empty", vbInformation
Exit Sub
Else
n1 = .Fields(0).Value & ""
n2 = .Fields(1).Value & ""
n3 = .Fields(2).Value & ""
n4 = .Fields(3).Value & ""
n5 = .Fields(4).Value & ""
End If
End With
End Sub

===============Form2==========================

Private Sub Command1_Click()
Form1.n1 = catalog.Text
Form1.n2 = description.Text
Form1.n3 = price.Text
Form1.n4 = contactp.Text
Form1.n5 = store.Text
Unload Me
End Sub

==================Module1========================= ==

Option Explicit
 
Public cn As New ADODB.Connection
Public rs As New ADODB.Recordset
 
Public Sub opencars()
With cn
.Provider = "Microsoft.Jet.OLEDB.4.0"
.ConnectionString = App.Path & "\books.mdb"
.Open
End With
End Sub
 
Public Sub opengas()
With rs
.Source = "Select * from books"
.CursorLocation = adUseClient
.CursorType = adLockOptimistic
.ActiveConnection = cn
.Open
End With
End Sub
 
Public Function retrieve(ByVal recset As ADODB.Recordset, ByVal qry As String) As ADODB.Recordset
With recset
If recset.State = adStateOpen Then
.Close
End If
.Source = qry
.CursorType = adOpenKeyset
.CursorLocation = adUseClient
.LockType = adLockOptimistic
.ActiveConnection = cn
.Open
End With
Set retrieve = recset.DataSource
End Function
 
Public Sub execute(ByVal qry As String)
On Error GoTo Err
With cn
.BeginTrans
.execute qry
.CommitTrans
End With
 
Err:
cn.RollbackTrans
MsgBox Err.description
End Sub
 

[debasisdas]

Do not unload the second form just hide it and get back to the first form.

[panget]

I got rid of Unload Me. But the machine returned the error, "Syntax Error in INSERT INTO Statement"

[panget]

I've resolved the add problem. Thank you.

But now, I'd like to make an update button.

For form1, I will add this script:

--Form1--
Private Sub Command2_Click()
Form3.show vbmodal
Dim strQry2 As String
strQry2 = "update books set category = '" & n1 & "', description = '" & n2 & "', price = '" & n3 & "', contact_P = '" & n4 & "', store = '" & n5 & "', where category = '" & n1 & "'"
ab.execute strQry2
cd.Requery
Call Display
End Sub
---------

and for the new form

--Form3--
Public cd as new adodb.recordset

Private Sub Command3_Click()
With cd
If Not .EOF Then
.MoveNext
If .EOF Then
.MoveLast
End If
End If
Call Display
End With
End Sub
Private Sub Command2_Click()
With cd
If Not .BOF Then
.MovePrevious
If .BOF Then
.MoveFirst
End If
End If

Call Display
End With
End Sub

Public Sub Command4_Click()
Form1.n1 = catalog.Text
Form1.n2 = description.Text
Form1.n3 = price.Text
Form1.n4 = contactp.Text
Form1.n5 = store.Text
Form3.Hide
End Sub

----------
What should happen is if I click the Update button (Command2) on Form1, The contents of the flexgrid should appear on textboxes on Form3. I should be able to navigate them through the previous and next buttons (Command2 and 3 in Form3)