Regarding Adp Project

[bill]

I have a adp form hooked to a sql database
I have a table called zipcodes
I also have another table called main

I have 2 fields

one field is a text box and the other is a combo box

I want to be able to type in the zipcode in the textbox
and have the combo box populate with all the citys from
that zipcode. I have it working in mdb's but not adp
projects.

Thanks in advance

 

[J. M. De Moor]

Use a stored procedure that takes the value of the text box as the input
parameter.

CREATE PROCEDURE GetCities @zip AS VARCHAR(9)
AS
SELECT city FROM ZipCodes
WHERE zip_code = COALESCE(@zip, zip_code)
;

In the city combobox on the Access form, change the RowSourceType to
Table/View/StoredProc. Then in your event handler (the text box On Exit?)
write:

Dim SQLString As String
SQLString = "EXEC dbo.GetCities " & txtZipCode.Value
cbxCity.RowSource = SQLString
cbxCity.Requery

Joe

 

[Guest]

totally that was the right answer

of course, you can also bind the form to the stored proc directly, and then
the parameter will be auto-negotiated with the value from a control of that
name.

IE-- you have a sp looking for param @zip_code

if you have a formfield name zip_code it will automatagically autonegotiate
that.

it is VERY VERY VERY powerful, and i think that it is offically unsupported
by MSFT (as i have never seen MS documentation that includes this practice)

HTH
-aaron

 

[J. M. De Moor]

Aaron

it is VERY VERY VERY powerful, and i think that it is offically unsupported
by MSFT (as i have never seen MS documentation that includes this practice)

Yup. Powerful but dangerous if different people maintain the program and
database. Anything that is undocumented (the technique you describe is
actually quite popular) can go away without notice. But, more importantly,
this is kind of like using named parameters in VBA: if the names change, the
routine stops working. Be careful...

Joe

 

[Rick Phillips]

Hi Bill I had the same problem using a list box on a form see the code I
used::
Form using a List Box

VBA Code: " This has to be used to properly pass parameters to a Stored
Procedure using a Form / List-Combo box.

Private Sub Last_Click()
' --- This resets the query for the last names ----
Dim FoundLastName As String

FoundLastName = Forms![frm_record_find].FindLastName
FoundLastName = "%" + FoundLastName + "%"

Me.List19.RowSource = "Exec FindLastName @LName = ' " &
FoundLastName & " ' "

Me.Repaint ' Save data for query
Me.Refresh ' Requery drop down listing
End Sub

Stored Procedure used:

Alter Procedure FindLastName
@LName as varchar(30)
As
SELECT DISTINCT
buyers.store_id as StoreID, buyers.buy_lname as Last,
buyers.buy_fname as First, stores.store_name as Store
FROM buyers INNER JOIN
stores ON buyers.store_id = stores.store_id
WHERE (buyers.buy_lname LIKE @LName)
ORDER BY buyers.buy_lname
return

 

[J. M. De Moor]

Rick

Me.Refresh ' Requery drop down listing

According to MS documentation, Refresh "updates only data that already
exists in your datasheet or form. It doesn't reorder records, display new
records, or remove deleted records and records that no longer meet specified
criteria." Which is why I normally use Requery. However, if Refresh works
for you, it is probably less resource consumptive.

Joe