Converting DLookups in Access DB Backended in SQL Server

[RoadrunnerII]

What this part of the form does is based on a Product numberID the Dlookups bring up the appropriate info for that product number
So here are the 4 Dlookups with the field names:

Manufacturer
=DLookUp("[Manufacturer]","nonserializedproducts","[ProductNumber] = '" & [Forms]![frm_NonSerializedInventory]![ProductNumberID] & "'")

Product Description:
=DLookUp("[ProductDescription]","nonserializedproducts","[ProductNumber] = '" & Forms!frm_NonSerializedInventory!ProductNumberID & "'")

Inventory Type:
=DLookUp("[Type]","qryNonSerializedInvTypes","[ProductNumber] = '" & Forms!frm_NonSerializedInventory!ProductNumberID & "'")

Product Notes:
=DLookUp("[Notes]","nonserializedproducts","[ProductNumber] = '" & Forms!frm_NonSerializedInventory!ProductNumberID & "'")

When the form loads it takes about 10 seconds or more on WAN link with a Calculating in the bottom Right
The rest of the form is up and showing only these 4 Dlookup fields are empty until the Calculating is completed
I am thinking it is the Dlookups that are slowing it down because the tables are on the SQL server

So is there a better way to do these than the Dlookups?

RRII

 

[boblarson]

Why not just include the info in the combo box in fields displayed with 0" and just reference the combo. Should be much faster.

 

[pbaldy]

Try this in the Current event:

  Dim strSQL        As String
  Dim db            As DAO.Database
  Dim rs            As DAO.Recordset

  Set db = CurrentDb()

  strSQL = "SELECT Manufacturer, ProductDescription, Type, Notes " _
         & "FROM nonserializedproducts " _
         & "WHERE [ProductNumber] = '" & Forms!frm_NonSerializedInventory!ProductNumberID & "'"
  Set rs = db.OpenRecordset(strSQL, dbOpenDynaset)

  Me.Manufacturer = rs!Manufacturer
  Me.ProductDescription = rs!ProductDescription
  'etc

  Set rs = Nothing
  Set db = Nothing

 

[pbaldy]

Ah, that's what happens when you have to go help a user while typing a response. If the product is in a combo, Bob is correct about getting the values from there.

 

[RoadrunnerII]

Dlookup in Form boxes

These particular boxes are text boxes
We did not want the user to be able to change the data in the boxes as it is descriptive to the Product Number
The user can change the Product Number and the Dlookup fields will change with it
I cut and pasted a view of this part of the form
The second attachment is in design view showing the fields with the DLookups

RRII

 

[pbaldy]

Then Bob's suggestion is better (I didn't pick up on the combo). In the row source of your combo, include those other fields (presuming they all come from the same table as the displayed field). Make sure you adjust the column count and column width properties, then simply use this as the control source of the textboxes:

=ComboName.Column(x)

where x is the number of the column containing the desired field (and the column property is zero based).

 

[RoadrunnerII]

Form Dlookup

Here is the Row Source info from the Product ID Combo box
SELECT [nonserializedproducts].[ProductNumber] FROM [nonserializedproducts] ORDER BY [ProductNumber];

So I add the Manufacturer, Product Description and Inventory Type columns to the row source of the combo box
Right?
Like This
SELECT [nonserializedproducts].[ProductNumber], [nonserializedproducts].[Manufacturer], [nonserializedproducts].[InventoryType], [nonserializedproducts].[ProductDescription], [nonserializedproducts].[Notes] FROM [nonserializedproducts] ORDER BY [ProductNumber];

Then in the Text boxes I change out the Dlookups to ??
I looked for a parameter for the Column count and Column Width but not there for Text Boxes???

Just need a bit of clarity
Been working on this every night and most everyday inbetween my real job duties to try and get it running right
Getting a bit punchy

RRII

 

[boblarson]

Then in the Text boxes I change out the Dlookups to ??

In the After Update event of the combo put:

Me.YourTextBoxName = Me.YourComboName.Column(x)

where x is the column of the data you want in that text box.

As Paul mentioned, it is zero based, so if you want column 2 you would put .Column(1) and if you wanted column 5 you would use .Column(4)

 

[RoadrunnerII]

I'm confused
You keep talking about the combo box but only 1 of the boxes is a combo
The first one for Product Number
The 4 below are just text boxes
I understand adding the specific fields to the combo box no problem
I just don't get the mechanics of the Txt boxes


So how does this populate the Txt boxes which are not Combo boxes
I must be getting thick here

RRII

 

[boblarson]

The code is what assigns the values to the text box from the columns in the combo.

 

[pbaldy]

You're letting the combo get all 4 fields for you. All you need in the textboxes is:

=ComboName.Column(x)

to display the values. That will look at the appropriate column of the combo and display the value associated with the chosen value.

 

[RoadrunnerII]

Dlookup in Form boxes

So
Here is my Row Source for the combo box
SELECT [nonserializedproducts].[ProductNumber], [nonserializedproducts].[Manufacturer], [nonserializedproducts].[InventoryType], [nonserializedproducts].[ProductDescription], [nonserializedproducts].[Notes] FROM [nonserializedproducts] ORDER BY [ProductNumber],[Manufacturer],[ProductDescription],[InventoryType],[Notes];

Here is the Event Proc on the Combo Box

Private Sub Product_Number_ID_AfterUpdate()
Me.DLManufacturer = Me.DLManufacturer.Column(1)
Me.DLManufacturerType = Me.DLManufacturerType.Column(2)
Me.DLInventoryType = Me.DLInventoryType.Column(3)
Me.DLProductNotes = Me.DLProductNotes.Column(4)
End Sub

Here is the Control Source for each Txt Box
DLManufacturer
=[Product Number ID].Column(1)
DLManufacturerType
=[Product Number ID].Column(2)
DLInventoryType
=[Product Number ID].Column(3)
DLProductNotes
=[Product Number ID].Column(4)

So only the DLmanufacturer the first column is populating
Is it my Event Procedure Syntax?

RRII

 

[pbaldy]

You want one technique or the other, not both (either unbound textboxes and the code, or no code and the control source in the textbox). If you're only getting the first one populated, make sure the column count property of the combo is 5.

 

[RoadrunnerII]

Got it finally Took a bit of understanding that the Select order is the Column count not the Group by order
Got one bit of a surprise in that the Inventory Type is actually populates from a query not the same place as the combo box
So could I create a combo box to pull the data in for the Inventory Type but make it invisible and use the same technic?
My twisted brain logic thinks it should work

RRII

 

[boblarson]

Depending on that query, you might actually be able incorporate it into the single combo query. Post the SQL from both queries so we can see.

 

[RoadrunnerII]

Dlookups

Here is the SQL from the Product Number Combo box which now does 3 of the Txt boxes for me
SELECT [nonserializedproducts].[ProductNumber], [nonserializedproducts].[Manufacturer], [nonserializedproducts].[ProductDescription],[nonserializedproducts].[InventoryType], [nonserializedproducts].[Notes] FROM [nonserializedproducts] ORDER BY [ProductNumber],[Manufacturer],[ProductDescription],[InventoryType],[Notes];
The Inventory Type is actually a reference number that does not provide the correct information
The Correct Inventory Type information I need is in this Dlookup statement
=DLookUp("[Type]","qryNonSerializedInvTypes","[ProductNumber] = '" & Forms!frm_NonSerializedInventory!ProductNumberID & "'")
Here is the sql for the qryNonSerializedInvTypes
SELECT nonserializedproducts.ProductNumber, InventoryType.Type
FROM InventoryType INNER JOIN nonserializedproducts ON InventoryType.TypeID = nonserializedproducts.InventoryType;


The Type field actual gives me the right Data
So is there a way to merge the qryNonSerializedInvTypes.Type into the Select from the Combo Box above?
It would be great if we can!
If I can solve this Dlookup I have two left on the this form
Both are stinkers as they are Text boxes as well
I will wait on them until I get this one fixed
These DLookups are just killing this form when you open it on a link like me VPN'd in from home on a Wireless ISP 200-512k link.
But it really gives me the real life of what it would be like on the other side of the country to use!
Thanks again for all the great help!
RRII

 

[boblarson]

I said SQL from BOTH queries. What is the SQL for qryNonSerializedInvTypes? Not the DLookup you were using it in, what is the actual SQL???

 

[RoadrunnerII]

Bob
I added the actual sql for the query into the post above
Sorry It was getting late

RRII

 

[boblarson]

Not 100% certain about the final syntax of this as I usually use the QBE grid, but this shouild get it:

SELECT [nonserializedproducts].[ProductNumber], [nonserializedproducts].[Manufacturer], 
[nonserializedproducts].[ProductDescription],[nonserializedproducts].[InventoryType], 
[nonserializedproducts].[Notes], [color=red][InventoryType].[Type][/color]
FROM [nonserializedproducts], [color=red][InventoryType][/color]
[color=red]INNER JOIN nonserializedproducts ON InventoryType.TypeID = nonserializedproducts.InventoryType[/color]
ORDER BY [ProductNumber],[Manufacturer],[ProductDescription],[InventoryType],[Notes];

 

[RoadrunnerII]

When I put the code in the Combo box as the Row Source I get an error in the FROM from the Sql Statement Query Builder in Access
I must be getting thick again
This should be obvious to me
RRII