32
I created a Database which I named "Address".
I went through the Control Panel and created a DSN to enable me connect to the Database through ODBC.
I then created a Form with the same fields as that of the Database. I wrote the necessary codes and ran the Program. Below are my codes and the runtime error I received:
Run-Time Error -2147217904(80040e10):
[Microsoft] [ODBC Microsoft Access Driver]
Too few parameters.
Expected 1
My codes:
' Stores the id of the current address
Dim intCurrAddress As Integer
' Indicates whether a change in the
' list box should invoke a save of the
' data in the text boxes
Dim intSaveUpdate As Integer
' This subroutine should be called to load or
' refresh the combo box of addresses
Private Sub LoadAddresses()
' Declare our variables
Dim dbAddress As New ADODB.Connection
Dim rsAddress As New ADODB.Recordset
' Make the connection
dbAddress.Open "dsn=address"
' Clear the combo box
cboAddresses.Clear
' Request the addresses with a SQL statement
' executed by the connection to the database
Set rsAddress = dbAddress.Execute("select * from " & _
"address order by txtLastName")
' Set the global variable to the first address
' since that is the one that will be displayed
intCurrAddress = rsAddress("idAddress")
' Loop through the addresses
Do Until rsAddress.EOF
' Add the address to the list by showing the
' persons first name and last name
cboAddresses.AddItem _
rsAddress("txtFirstName") & " " & _
rsAddress("txtLastName")
' Add the id of the address to the item data
' property for the address
cboAddresses.ItemData(cboAddresses.NewIndex) = _
rsAddress("idAddress")
' Move to the next record
rsAddress.MoveNext
Loop
' Close the DB
dbAddress.Close
' Set our save variable to indicate no saves should
' be done when the index is set
intSaveUpdate = 0
' Set to the first address
cboAddresses.ListIndex = 0
' Set back to allow updates
intSaveUpdate = 1
End Sub
' Click event of the combo box. Note that this
' is fired as well when the listindex property
' is set.
Private Sub cboAddresses_Click()
' Declare our variables
Dim dbAddress As New ADODB.Connection
Dim rsAddress As New ADODB.Recordset
' Open our connection
dbAddress.Open "dsn=address"
' Check the global variable to see if we are supposed
' to save any changes entered by the user
If intSaveUpdate = 1 Then
' Build the SQL update query
sql = "update address set " & _
"txtFirstName = '" & txtFirstName.Text & "', " & _
"txtLastName = '" & txtLastName.Text & "', " & _
"txtAddress = '" & txtAddress.Text & "', " & _
"txtCity = '" & txtCity.Text & "', " & _
"txtState = '" & txtState.Text & "', " & _
"txtZipCode = '" & txtZipCode.Text & "', " & _
"txtPhone = '" & txtPhone.Text & "', " & _
"txtFax = '" & txtFax.Text & "' where idaddress = " & intCurrAddress
' Execute the query
dbAddress.Execute sql
End If
' Set the id of the current address
intCurrAddress = cboAddresses.ItemData(cboAddresses.ListIndex)
' Build the query to retrieve the address
' data
Set rsAddress = dbAddress.Execute("select * " & _
"from address where idAddress = " & intCurrAddress)
' Display the data
lblIDAddress.Caption = rsAddress("idAddress")
txtFirstName.Text = rsAddress("txtFirstName")
txtLastName.Text = rsAddress("txtLastName")
txtAddress.Text = rsAddress("txtAddress")
txtCity.Text = rsAddress("txtCity")
txtState.Text = rsAddress("txtState")
txtZipCode.Text = rsAddress("txtZipCode")
txtPhone.Text = rsAddress("txtPhone")
txtFax.Text = rsAddress("txtFax")
' Close the database
dbAddress.Close
End Sub
' Click event of the add button
Private Sub cmdAdd_Click()
' Enable the cancel and update buttons
cmdCancel.Enabled = True
cmdUpdate.Enabled = True
' Disable the add button
cmdAdd.Enabled = False
' Disable the combo list
cboAddresses.Enabled = False
' Clear the input text boxes
lblIDAddress.Caption = ""
txtFirstName.Text = ""
txtLastName.Text = ""
txtAddress.Text = ""
txtCity.Text = ""
txtState.Text = ""
txtZipCode.Text = ""
txtPhone.Text = ""
txtFax.Text = ""
' Ensure no are done (not really possible
' since the combo box is disabled)
intSaveUpdate = 0
End Sub
' Click event of the cancel button
Private Sub cmdCancel_Click()
' Load the address into the combo box
LoadAddresses
' Enable the combo box
cboAddresses.Enabled = True
' Enable the add button
cmdAdd.Enabled = True
' Disable the update button
cmdUpdate.Enabled = False
' Diable the cancel button
cmdCancel.Enabled = False
End Sub
' Click event of the delete button
Private Sub cmdDelete_Click()
' Declare our variables
Dim dbAddress As New ADODB.Connection
Dim intResponse As Integer
' Query the user to ensure they want to
' delete the record
strResponse = MsgBox("Are you sure?", vbYesNo, "Delete Query")
' Check to see if the response was a yes
If strResponse = 6 Then
' Open the connection
dbAddress.Open "dsn=address"
' Delete the address
sql = "delete from address where idaddress = " & intCurrAddress
' Execute the SQL statement
dbAddress.Execute sql
' Load the addresses
LoadAddresses
' Close the DB
dbAddress.Close
End If
End Sub
' Click event of the Update button
Private Sub cmdUpdate_Click()
' Declare the variables
Dim dbAddress As New ADODB.Connection
Dim rsAddress As New ADODB.Recordset
' Disable the update button
cmdUpdate.Enabled = False
' Enable the add button
cmdAdd.Enabled = True
' Enable the combo boxes
cboAddresses.Enabled = True
' Open the database connection
dbAddress.Open "dsn=address"
' Build the insert SQL statement
sql = "insert into address(txtFirstName, txtLastName, txtAddress, " & _
"txtCity, txtState, txtZipCode, txtPhone, txtFax)" & _
" values('" & _
txtFirstName.Text & "', '" & _
txtLastName.Text & "', '" & _
txtAddress.Text & "', '" & _
txtCity.Text & "', '" & _
txtState.Text & "', '" & _
txtZipCode.Text & "', '" & _
txtPhone.Text & "', '" & _
txtFax.Text & "')"
' Execute the SQL statement
dbAddress.Execute sql
' Close the DB connection
dbAddress.Close
' Indicate record changes can now be saved
intSaveUpdate = 1
' Load the addresses
LoadAddresses
End Sub
Private Sub Form_Load()
' Load the addresses
LoadAddresses
End Sub
Please kindly help me examine the codes and help me detect where I am missing it.
Thank you.
Akinyemi.
