January 22, 2006 at 7:27 am
I'm working on my first ADP with SQL Server, I'm familiar with using Access JET databases and I have a form where I would like to populate some bound text boxes using VB code based on a value the user selects with a combo box.
If I were just creating an access database I'd probably have something like this as the AfterUpdate event for the combo box:
Set rsCustomers = New ADODB.Recordset
rstrsCustomers.CursorLocation = adUseClient
rstrsCustomers.Open "SELECT * FROM Customers WHERE CustomerID ='" & Me!Combo1 & "';", CurrentProject.Connection, adOpenStatic
If Not rsCustomers.EOF Then
Me!CustomerName = rsCustomers!CustomerName
Me!CustomerTelephone = rsCustomers!CustomerTelephone
End If
rsCustomers.Close
Is this still OK for use in an ADP, I mean I know it works OK but is it the best way or should I be constructing and executing SQL or would using a stored procedure be more efficient ?
Steve
January 23, 2006 at 6:35 am
Whether you are working with an mdb or adp, I believe the most efficient means of doing this would be having some extra columns (possibly hidden, ie width=0) in the combo box. For example, if you have Cust ID as the 1st, Name as the 2nd, and Phone as the 3rd then something like:
Set ctl = Me!Combo1
Me!CustomerName = Nz(ctl.Column(1))
Me!CustomerPhone = Nz(ctl.Column(2))
will do the trick without having to make another database call at all. But if you are going to go out to the database, I believe a stored procedure would do it more efficiently, but with a small table/application, I doubt you would see any real difference.
Dick
January 23, 2006 at 8:30 am
January 23, 2006 at 1:33 pm
Thanks for the good advice guys.
Steve
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply