If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
DLookUp and Continuous forms
Background: I have an .adp linked to SQL 2005. We have a continuous
form that is based off a select query that populates a field called 'material'. We need a continuous form here because 1) we need to have combo boxes to select data for an insert (I have an ADO insert script written - not the problem here) and 2) we need to see all the materials (query rows) at once. In the On Current event, I have vba code that parses the material field and does a DLookup and returns the material description. The material field can contain two different formats of materialID, thus the need to parse the ID and do a DLookup on the appropriate table. If you would like to see my VBA code please ask but I don't believe the code is the issue because it works. Problem: The description field populated by the DLookup always shows the value for the selected form. When I select a different recordset, the DLookup on all the continuous form instances changes to show the description of the material of the selected form. All the DLookups are always the same for each instance of the continuous form, and only show the selected records material description. Please help me figure out how to have the returned DLookup value be unique for each record on the form. Is there another event I should put the DLookup code in, or is there a way to specify uniqueness that I don't know about? Thanks! Matt |
#2
|
|||
|
|||
DLookUp and Continuous forms
Personally I would avoid using dlookup on a continuous form - find another
way - but I think I see what you're getting at. The reason for your problem is that Access regards every instance of controls on a continuous form as the the same control. The only thing that differentiates them is the data they contain, through their data bindings (controlsource property). If you change the value for one, or any property, they all change. The answer is not to update the field from code at all, but instead use the dlookup expression (possibly wrapped in a iif) as the controlsource of the description field. For example: =iif(not isnull(material),dlookup("description","tblMateria l","material=""" & material & """"),"Please select a material") If you need to pre-parse the "material" field then use a function like: =iif(not isnull(material),dlookup("description","tblMateria l","material=""" & parseMaterialToMaterialCode(material) & """"),"Please select a material") If you need a different dlookup entirely dependent on the value of material, then wrap the whole thing in a function, binding the control source to: =parseMaterialFieldAndDLookupDescription(nz([material],"")) Your function accepts the value of the material and returns whatever you want in the Description field, using as much code as you like. However, I stress that using Dlookups on continuous forms is generally rather slow and inefficient. You might be better off using the function idea, working with the recordsets as global variables which are opened with the Form OnLoad event?? Let me know if this points in the right direction. Ben. wrote in message ups.com... Background: I have an .adp linked to SQL 2005. We have a continuous form that is based off a select query that populates a field called 'material'. We need a continuous form here because 1) we need to have combo boxes to select data for an insert (I have an ADO insert script written - not the problem here) and 2) we need to see all the materials (query rows) at once. In the On Current event, I have vba code that parses the material field and does a DLookup and returns the material description. The material field can contain two different formats of materialID, thus the need to parse the ID and do a DLookup on the appropriate table. If you would like to see my VBA code please ask but I don't believe the code is the issue because it works. Problem: The description field populated by the DLookup always shows the value for the selected form. When I select a different recordset, the DLookup on all the continuous form instances changes to show the description of the material of the selected form. All the DLookups are always the same for each instance of the continuous form, and only show the selected records material description. Please help me figure out how to have the returned DLookup value be unique for each record on the form. Is there another event I should put the DLookup code in, or is there a way to specify uniqueness that I don't know about? Thanks! Matt |
#3
|
|||
|
|||
DLookUp and Continuous forms
The field you are populating is an unbound textbox and thus that is why
you are getting the same value for every row. I can think of a couple of things you might try. 1. Put the DLookup in the Data Source of the text box...=YourDLookup 2. If there are other things that need to happen before the Dlookup (you said something about parsing), create a function in a module that you call and the returned value is put into the textbox. Here's how to do the function if you don't know already. Create a module and make a function, say GetDescription so now you should have Public Function GetDescription(). Change it a bit to Public Function GetDescription(ID as String) as String. Now put your code that you currently have here. Your last statement should be GetDescription = YourDLookupStatement Now in the textbox on your form, put =GetDescription([ID]) You will not need the On Current Event. Hope that helps! |
#4
|
|||
|
|||
DLookUp and Continuous forms
Thank you both Jeff and Ben for your prompt and informative responses.
I was able to fix my problem with your guidance. I created a public function which did the lookup for me and set the control source of the txtbox to be that function. Brilliant! -Matt Jeff L wrote: The field you are populating is an unbound textbox and thus that is why you are getting the same value for every row. I can think of a couple of things you might try. 1. Put the DLookup in the Data Source of the text box...=YourDLookup 2. If there are other things that need to happen before the Dlookup (you said something about parsing), create a function in a module that you call and the returned value is put into the textbox. Here's how to do the function if you don't know already. Create a module and make a function, say GetDescription so now you should have Public Function GetDescription(). Change it a bit to Public Function GetDescription(ID as String) as String. Now put your code that you currently have here. Your last statement should be GetDescription = YourDLookupStatement Now in the textbox on your form, put =GetDescription([ID]) You will not need the On Current Event. Hope that helps! |
Thread Tools | |
Display Modes | |
|
|