Update Forms Box with SQL

  • Thread starter Thread starter Tim
  • Start date Start date
T

Tim

I have not had any luck to get this forms box to update
when I doubleclick it...can anyone tell me what is wrong
with the coding???

Private Sub SalesTaxRate_DblClick(Cancel As Integer)
Me.SalesTaxRate = "SELECT [City and County].[Tax
Rate]FROM [City and County] WHERE Me.ShipCity = [City and
County].[City]"
End Sub

I get a runtime error -2147352567(80020009) The value you
entered isn't valid for this field.
Thanks,
 
You may not have a reference to the ADO object library.
In the Visual Basic editor, select the Tools>References
dialog from the menu. Be sure the Microsoft ActiveX Data
Objects 2.x Library is selected.
I think Howard's code will work with the changes I made.
See the thread on that as well.
-----Original Message-----
Using Access 2002 with .mdb
Tried your code and error box came up with compile error:
User-defined type not defined. This was on the statement
dim rst as New ADODB.Recordset

-----Original Message-----
You need to use a recordset object to accomplish this.
Are you using Access 2k or above? Are you using an
Access .adp/ade or .mdb/.mde?

Access 2K/XP Code
-----------------
Private Sub SalesTaxRate_DblClick(Cancel As Integer)
On Error Goto HandleError

'holds the recordset you want to get the data from
dim rst as New ADODB.Recordset

'holds the value for the textbox on your form
dim strShipCity as String

'pass the value to the variable
strShipCity=me.ShipCity

'get the record that you want
rst.open "SELECT [City and County].[Tax
Rate]FROM [City and County] WHERE [City and
County].[City]='" & strShipCity
& "'",CurrentProject.Connection

'if there are some records to be had, set
'the text box equal to the value in the FIRST record
'returned.
'Your query should be configured in a way
'which returns 1 record at the most
If Not rst.EOF And Not rst.BOF Then
me.SalesTaxRate=rst("Tax Rate")
End If

ExitHere:
Exit Sub
HandleError:
Msgbox err.description & "(" & err.number & ")"
Resume ExitHere
End Sub


Access 97 Code
-----------------
Private Sub SalesTaxRate_DblClick(Cancel As Integer)
On Error Goto HandleError

'holds the recordset you want to get the data from
dim rst as Recordset

'holds the value for the textbox on your form
dim strShipCity as String

'pass the value to the variable
strShipCity=me.ShipCity

'get the record that you want
Set rst=CurrentDB.openrecordset ("SELECT [City and
County].[Tax Rate]FROM [City and County] WHERE [City and
County].[City]='" & strShipCity & "'",dbOpenSnapShot)

'if there are some records to be had, set
'the text box equal to the value in the FIRST record
'returned.
'Your query should be configured in a way
'which returns 1 record at the most
If Not rst.EOF And Not rst.BOF Then
me.SalesTaxRate=rst("Tax Rate")
End If

ExitHere:
Exit Sub
HandleError:
Msgbox err.description & "(" & err.number & ")"
Resume ExitHere
End Sub

-----Original Message-----

I have not had any luck to get this forms box to update
when I doubleclick it...can anyone tell me what is wrong
with the coding???

Private Sub SalesTaxRate_DblClick(Cancel As Integer)
Me.SalesTaxRate = "SELECT [City and County]. [Tax
Rate]FROM [City and County] WHERE Me.ShipCity = [City and
County].[City]"
End Sub

I get a runtime error -2147352567(80020009) The value you
entered isn't valid for this field.
Thanks,
.
Click to expand...
.
Click to expand...
.
Click to expand...
 
Back
Top