'===================================================================================================================

Each of the following VB Code sections were created with one click using SQL VB Code Generator by NewRAD, Inc. 

Download at: http://www.newrad.com/software/sqlvbcodegenerator/


SQL VB Code Generator created the following VB Code sections with the 
typical coding jobs in mine:  Creating Add/Update edit screens and grids with
a list of records.  The grid is typically used to view a selection
of records and from there a user can typically double-click on a 
record and thus fill an Edit screen where the user can edit the record
and save their changes. The user can also typically click on an add button 
to add a new record.  

-----------------------------------------------------------------------------------------------------






'===================================================================================================================
Select

This is a typical "Select" clause to retrieve a record from a table and fill an edit screen.
All you do is select the table ("Publishers") and key field (in this case "gPubID") 

	strSQL = "SELECT * FROM Publishers "
	strSQL = strSQL & "WHERE PubID = " & gPubID
	strSQL = strSQL & "ORDER BY PubID
	gRS.Open strSQL, gDb 
	If Not gRS.EOF Then
		gRS.MoveFirst
		txtName = gRS("Name")
		txtCompany Name = gRS("Company Name")
		txtAddress = gRS("Address")
		txtCity = gRS("City")
		txtState = gRS("State")
		txtZip = gRS("Zip")
		txtTelephone = gRS("Telephone")
		txtFax = gRS("Fax")
		txtComments = gRS("Comments")
	End If
	Set gRS = Nothing
--------------------------------------------------------------------------------------------------------------------





'===================================================================================================================
Insert

	strSQL = "INSERT INTO Publishers(Name, [Company Name], Address, City, State, "
	strSQL = strSQL & "VALUES ("
	strSQL = strSQL & "'" & Pad(txtName) & "', '" & Pad(txtCompany Name) & "', '" & Pad(txtAddress) & "', "
	strSQL = strSQL & "'" & Pad(txtCity) & "', '" & Pad(txtState) & "', '" & Pad(txtZip) & "', "
	strSQL = strSQL & "'" & Pad(txtTelephone) & "', '" & Pad(txtFax) & "', '" & Pad(txtComments) & "') "
	strSQL = StringReplace(strSQL, "'',", "Null,")
	strSQL = StringReplace(strSQL, "'')", "Null,")
	gDb.Execute strSQL
--------------------------------------------------------------------------------------------------------------------





'===================================================================================================================
Update

	strSQL = "UPDATE Publishers SET "
	strSQL = strSQL & "Name = '" & Pad(txtName) & "', Company Name = '" & Pad(txtCompany Name) & "', "
	strSQL = strSQL & "Address = '" & Pad(txtAddress) & "', City = '" & Pad(txtCity) & "', "
	strSQL = strSQL & "State = '" & Pad(txtState) & "', Zip = '" & Pad(txtZip) & "', "
	strSQL = strSQL & "Telephone = '" & Pad(txtTelephone) & "', Fax = '" & Pad(txtFax) & "', "
	strSQL = strSQL & "Comments = '" & Pad(txtComments) & "' "
	strSQL = strSQL & "WHERE PubID = " & gPubID
	strSQL = StringReplace(strSQL, "'',", "Null,")
	strSQL = StringReplace(strSQL, "'' ", "Null ")
	gDb.Execute strSQL
--------------------------------------------------------------------------------------------------------------------





'===================================================================================================================
Insert/Update 

This is what might be typical code behind a "Save" button on an edit screen written in VB.
If it is a new record then it does an Insert query. If the record already
exists, then it Updates the existing record. After it saves the record, it
can refill a selection grid if one exists.  Get "FillGrid" subroutine from the
"FillGrid" VB Code Generator menu item.


	On Error GoTo ErrorSave
	OpenConnection App.Path & "\" & App.Title & ".mdb
	If gAddMode = True Then
		strSQL = "INSERT INTO Publishers(Name, [Company Name], Address, City, State, "
		strSQL = strSQL & "VALUES ("
		strSQL = strSQL & "'" & Pad(txtName) & "', '" & Pad(txtCompany Name) & "', '" & Pad(txtAddress) & "', "
		strSQL = strSQL & "'" & Pad(txtCity) & "', '" & Pad(txtState) & "', '" & Pad(txtZip) & "', "
		strSQL = strSQL & "'" & Pad(txtTelephone) & "', '" & Pad(txtFax) & "', '" & Pad(txtComments) & "') "
		strSQL = StringReplace(strSQL, "'',", "Null,")
		strSQL = StringReplace(strSQL, "'')", "Null,")
		gDb.Execute strSQL
	Else
		strSQL = "UPDATE Publishers SET "
		strSQL = strSQL & "Name = '" & Pad(txtName) & "', Company Name = '" & Pad(txtCompany Name) & "', "
		strSQL = strSQL & "Address = '" & Pad(txtAddress) & "', City = '" & Pad(txtCity) & "', "
		strSQL = strSQL & "State = '" & Pad(txtState) & "', Zip = '" & Pad(txtZip) & "', "
		strSQL = strSQL & "Telephone = '" & Pad(txtTelephone) & "', Fax = '" & Pad(txtFax) & "', "
		strSQL = strSQL & "Comments = '" & Pad(txtComments) & "' "
		strSQL = strSQL & "WHERE PubID = " & gPubID
		strSQL = StringReplace(strSQL, "'',", "Null,")
		strSQL = StringReplace(strSQL, "'' ", "Null ")
		gDb.Execute strSQL
	End If
	CloseDb
	frmList.FillGrid
	Unload Me
	Set frmAddEdit = Nothing

ResumeSave:
	Exit Sub:

ErrorSave:
	MsgBox "Error No: " & Err & " " & Error
	Resume ResumeSave
	Resume 0
--------------------------------------------------------------------------------------------------------------------







'=================================================================================================

FillGrid

The FillGrid subroutine fills a grid in the order of the selected field, in this case "PubID".



	On Error GoTo ErrorFillGrid
	
	CenterForm Me
	
	OpenConnection App.Path & "\" & App.Title & ".mdb
	
	strSQL = "SELECT * FROM Publishers "
	strSQL = strSQL & "ORDER BY PubID
	gRS.Open strSQL, gDb
	Grid1.Cols = gRS.Fields.Count
	If Not gRS.EOF Then
		gRS.MoveFirst
		Grid1.Rows = gRS.RecordCount + 1
		Grid1.Row = 1
		Grid1.Col = 0
		Grid1.RowSel = Grid1.Rows - 1
		Grid1.ColSel = gRS.Fields.Count - 1
		Grid1.Clip = StringReplace(gRS.GetString, Chr(13) & Chr(10), "")
	End If
	
	For N = 0 to gRS.Fields.Count - 1
		Grid1.TextMatrix(0, N) = gRS.Fields(N).Name
	Next
	
	Grid1.ColWidth(0) = 800
	Grid1.ColWidth(1) = 4000
	Grid1.ColWidth(2) = 4000
	Grid1.ColWidth(3) = 4000
	Grid1.ColWidth(4) = 4000
	Grid1.ColWidth(5) = 4000
	Grid1.ColWidth(6) = 4000
	Grid1.ColWidth(7) = 4000
	Grid1.ColWidth(8) = 4000
	Grid1.ColWidth(9) = 4000
	
	For N = 0 To Grid1.Cols - 1
		Grid1.ColAlignment(N) = flexAlignLeftCenter
	Next
	
	Grid1.AllowUserResizing = flexResizeColumns
	
	Set gRS = Nothing
	CloseDb
	
ResumeFillGrid:
	Exit Sub:

ErrorFillGrid:
	MsgBox "Error No: " & Err & " " & Error
	Resume ResumeFillGrid
	Resume 0
	
--------------------------------------------------------------------------------------------------- 







'=================================================================================================

Under the "VB Code Generator" menu, there is a menu item for "String Prefix" 
for which you can select the string prefix "txt" or "str".  The code below illustrates the difference.
Generally, you would use "txt" when filling text boxes on an edit screen, and "str" when loading
the values of variables.
 


"txt" string prefix

	strSQL = "SELECT * FROM [All Titles] "
	strSQL = strSQL & "WHERE Title = " & gTitle
	strSQL = strSQL & "ORDER BY Title
	gRS.Open strSQL, gDb 
	If Not gRS.EOF Then
		gRS.MoveFirst
		txtISBN = gRS("ISBN")
		txtAuthor = gRS("Author")
		txtYear Published = gRS("Year Published")
		txtCompany Name = gRS("Company Name")
	End If
	Set gRS = Nothing



"str" string prefix

	strSQL = "SELECT * FROM [All Titles] "
	strSQL = strSQL & "WHERE Title = " & gTitle
	strSQL = strSQL & "ORDER BY Title
	gRS.Open strSQL, gDb 
	If Not gRS.EOF Then
		gRS.MoveFirst
		strISBN = gRS("ISBN")
		strAuthor = gRS("Author")
		strYear Published = gRS("Year Published")
		strCompany Name = gRS("Company Name")
	End If
	Set gRS = Nothing

--------------------------------------------------------------------------------------------------- 






'=================================================================================================
Additional common VB Subroutines 




Sub CenterForm(frm As Form)

    frm.Top = (Screen.Height - frm.Height) \ 2
    frm.Left = (Screen.Width - frm.Width) \ 2

End Sub





Function StringReplace(strText, strOld, strNew) As String

    Dim iLenOld As Integer
    Dim iLenNew As Integer
    Dim x As Integer


    If IsNull(strText) Then
        Exit Function
    End If

    iLenOld = Len(strOld)
    iLenNew = Len(strNew)

    x = InStr(1, strText, strOld, vbTextCompare)
    If x = 0 Then
        StringReplace = strText
        Exit Function
    Else
        Do Until x = 0
            strText = Mid(strText, 1, x - 1) & strNew & Mid(strText, x + iLenOld, Len(strText) - (x + iLenOld) + 1)
            x = InStr(x, strText, strOld, vbTextCompare)
        Loop
        StringReplace = strText
    End If

End Function





Function Pad(strText) As String

	StringReplace strText, """", "'" 

End Function
---------------------------------------------------------------------------------------------------