DLookup in subform

Using Access 2007:
Form - frmNewQuote
Continuous (Tabular) Subform - subfrmOptions with unitPrice textbox 
calculating price from value entered into materials field in the subform
Table taskMaterials with description field matching materials combobox in 
subfrmOptions (datatype is text) 
DLookup entered as control source for unitPrice textbox 
=DLookUp("[price]","[taskMaterials]","[description]=" & " [materials]")

This works OK (ie the correct price is displayed after material is selected 
from a combo) in the subform when it is displayed as just a form, but when 
attached as a subform to the main form, there is no display in the calculated 
field textbox. (Or, even worse, the first record entered will display the 
price, but subsequent fields don't)

I have also tried 
=DLookUp("[price]","[taskMaterials]","[description]=" & " 
[Forms]![frmNewQuote]![subformOptions].[Form]![materials]")
in the subform when embedded in the main form, and the result is a flashing 
#Error 

Please help me fix this. Thanks!
0
Utf
9/19/2007 4:40:02 AM
access.forms 6864 articles. 1 followers. Follow

11 Replies
1650 Views

Similar Articles

[PageSpeed] 57

Hi,

Try adding a single qoute in the Dlookup..

=DLookUp("[price]","[taskMaterials]","[description]= ' " & " 
[Forms]![frmNewQuote]![subformOptions].[Form]![materials] & " ' " ")

I spaced the qoutes so that you it better.

>kasab wrote:
>Using Access 2007:
>Form - frmNewQuote
>Continuous (Tabular) Subform - subfrmOptions with unitPrice textbox 
>calculating price from value entered into materials field in the subform
>Table taskMaterials with description field matching materials combobox in 
>subfrmOptions (datatype is text) 
>DLookup entered as control source for unitPrice textbox 
>=DLookUp("[price]","[taskMaterials]","[description]=" & " [materials]")
>
>This works OK (ie the correct price is displayed after material is selected 
>from a combo) in the subform when it is displayed as just a form, but when 
>attached as a subform to the main form, there is no display in the calculated 
>field textbox. (Or, even worse, the first record entered will display the 
>price, but subsequent fields don't)
>
>I have also tried 
>=DLookUp("[price]","[taskMaterials]","[description]=" & " 
>[Forms]![frmNewQuote]![subformOptions].[Form]![materials]")
>in the subform when embedded in the main form, and the result is a flashing 
>#Error 
>
>Please help me fix this. Thanks!

-- 
Please Rate the posting if helps you

Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/Forums.aspx/access-forms/200709/1

0
AccessVandal
9/19/2007 5:52:51 AM
Sorry, error there.

=DLookUp("[price]","[taskMaterials]","[description]= ' " &  
[Forms]![frmNewQuote]![subformOptions].[Form]![materials] & " ' " )


>kasab wrote:

-- 
Please Rate the posting if helps you

Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/Forums.aspx/access-forms/200709/1

0
AccessVandal
9/19/2007 6:00:41 AM
Thanks, but I now have #Name? displayed in unitCost field. Do you have any 
other suggestions please?

"AccessVandal via AccessMonster.com" wrote:

> Sorry, error there.
> 
> =DLookUp("[price]","[taskMaterials]","[description]= ' " &  
> [Forms]![frmNewQuote]![subformOptions].[Form]![materials] & " ' " )
> 
> 
> >kasab wrote:
> 
> -- 
> Please Rate the posting if helps you
> 
> Message posted via AccessMonster.com
> http://www.accessmonster.com/Uwe/Forums.aspx/access-forms/200709/1
> 
> 
0
Utf
9/19/2007 6:38:01 AM
Hi,

This error indicates Access cannot find the Control in your sub-form or the
Form's RecordSource Query is missing a field.

Check the RecordSource Field and the Control name. Else you may want to refer
the Dlookup to another method.

=DLookUp("[TableName].[price]","[taskMaterials]","[TableName].[description]=
' " &  
[Forms]![frmNewQuote]![subformOptions].[Form]![materials] & " ' " )

or

=DLookUp("[price]","[taskMaterials]","[description]= ' " &  
Me!materials & " ' " )

Note: Assuming the DLookup is in the Sub-Form. Me!materials is the subform
control name.

or

Forms!subformOption.material  to refer to a control if the Dlookup is in the
subform.

>kasab wrote:
>Thanks, but I now have #Name? displayed in unitCost field. Do you have any 
>other suggestions please?

-- 
Please Rate the posting if helps you

Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/Forums.aspx/access-forms/200709/1

0
AccessVandal
9/19/2007 7:12:13 AM
Thanks again for staying with me.

If I read you correctly, materials may be missing from the subform's fields? 
No. This is taken directly from another table, not a query. Also, both fields 
(description in taskMaterials table and materials in subfrmOptions) and the 
table have correct spelling in the DLookup.

=DLookUp("[TableName].[price]","[taskMaterials]","[TableName].[description]=
' " &  
[Forms]![frmNewQuote]![subfrmOptions].[Form]![materials] & " ' " ) 
isn't working.

Both materials and the DLookup textbox are in the subform.

Also, no difference with 
=DLookUp("[price]","[taskMaterials]","[description]= ' " &  
Me!materials & " ' " ) or 
=DLookUp("[price]","[taskMaterials]","[description]= ' " &  
Forms!subformOption.materials & " ' " )

If I use a (sub)subform based on a query instead of a DLookup, I'll need to 
lookup another field for another calculation, so that's not really an option 
either.

Any suggestions on where else to look? This punctuation thing has got me beat.

Thanks

"AccessVandal via AccessMonster.com" wrote:

> Hi,
> 
> This error indicates Access cannot find the Control in your sub-form or the
> Form's RecordSource Query is missing a field.
> 
> Check the RecordSource Field and the Control name. Else you may want to refer
> the Dlookup to another method.
> 
> =DLookUp("[TableName].[price]","[taskMaterials]","[TableName].[description]=
> ' " &  
> [Forms]![frmNewQuote]![subformOptions].[Form]![materials] & " ' " )
> 
> or
> 
> =DLookUp("[price]","[taskMaterials]","[description]= ' " &  
> Me!materials & " ' " )
> 
> Note: Assuming the DLookup is in the Sub-Form. Me!materials is the subform
> control name.
> 
> or
> 
> Forms!subformOption.material  to refer to a control if the Dlookup is in the
> subform.
> 
> >kasab wrote:
> >Thanks, but I now have #Name? displayed in unitCost field. Do you have any 
> >other suggestions please?
> 
> -- 
> Please Rate the posting if helps you
> 
> Message posted via AccessMonster.com
> http://www.accessmonster.com/Uwe/Forums.aspx/access-forms/200709/1
> 
> 
0
Utf
9/19/2007 7:52:01 AM
So, your’re saying the subform RecordSource is not part of the DlookUp.

Does the control “material” is the name of the control? Check the "material"
properties under the Tab "Other" and look the Name field.

>kasab wrote:
>Thanks again for staying with me.
>
>If I read you correctly, materials may be missing from the subform's fields? 
>No. This is taken directly from another table, not a query. Also, both fields 
>(description in taskMaterials table and materials in subfrmOptions) and the 
>table have correct spelling in the DLookup.
>
>=DLookUp("[TableName].[price]","[taskMaterials]","[TableName].[description]=
>' " &  
>[Forms]![frmNewQuote]![subfrmOptions].[Form]![materials] & " ' " ) 
>isn't working.
>
>Both materials and the DLookup textbox are in the subform.
>
>Also, no difference with 
>=DLookUp("[price]","[taskMaterials]","[description]= ' " &  
>Me!materials & " ' " ) or 
>=DLookUp("[price]","[taskMaterials]","[description]= ' " &  
>Forms!subformOption.materials & " ' " )
>
>If I use a (sub)subform based on a query instead of a DLookup, I'll need to 
>lookup another field for another calculation, so that's not really an option 
>either.
>
>Any suggestions on where else to look? This punctuation thing has got me beat.
>
>Thanks
>
>> Hi,
>> 
>[quoted text clipped - 24 lines]
>> >Thanks, but I now have #Name? displayed in unitCost field. Do you have any 
>> >other suggestions please?

-- 
Please Rate the posting if helps you

Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/Forums.aspx/access-forms/200709/1

0
AccessVandal
9/19/2007 8:48:47 AM
The other option that I can suggest is to use the "taskMaterials" table with
a materialID instead of using the "description" field as a lookup. Using the
"description" is a very bad idea to begin with. What if you have more than
one with the same description?

If all else fails, use the DlookUp to find a single item first, than with
that we'll try to narrow it down.

=DLookUp("[price]","[taskMaterials]","[description]= 'the description of the
material here'")

>kasab wrote:
>Thanks again for staying with me.
>If I read you correctly, materials may be missing from the subform's fields? 
>No. This is taken directly from another table, not a query. Also, both fields 
>(description in taskMaterials table and materials in subfrmOptions) and the 
>table have correct spelling in the DLookup.

-- 
Please Rate the posting if helps you

Message posted via http://www.accessmonster.com

0
AccessVandal
9/19/2007 8:57:37 AM
I like to add further, if the textbox "material" and if it is blank or empty
it will produce an error. Like what you have posted "#Error".

>kasab wrote:
>Thanks again for staying with me.

-- 
Please Rate the posting if helps you

Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/Forums.aspx/access-forms/200709/1

0
AccessVandal
9/19/2007 9:06:58 AM
Yes, the Name property of the control that contains the data on the subform 
that is used in the DLookup is "materials" as used in the DLookup. 

The subform RecordSource is the quotes table that contains the field 
"materials" (text datatype). 

And the DLookup works fine in the (sub)form displayed in form view on its 
own. 

"AccessVandal via AccessMonster.com" wrote:

> So, your’re saying the subform RecordSource is not part of the DlookUp.
> 
> Does the control “material” is the name of the control? Check the "material"
> properties under the Tab "Other" and look the Name field.
> 
> >kasab wrote:
> >Thanks again for staying with me.
> >
> >If I read you correctly, materials may be missing from the subform's fields? 
> >No. This is taken directly from another table, not a query. Also, both fields 
> >(description in taskMaterials table and materials in subfrmOptions) and the 
> >table have correct spelling in the DLookup.
> >
> >=DLookUp("[TableName].[price]","[taskMaterials]","[TableName].[description]=
> >' " &  
> >[Forms]![frmNewQuote]![subfrmOptions].[Form]![materials] & " ' " ) 
> >isn't working.
> >
> >Both materials and the DLookup textbox are in the subform.
> >
> >Also, no difference with 
> >=DLookUp("[price]","[taskMaterials]","[description]= ' " &  
> >Me!materials & " ' " ) or 
> >=DLookUp("[price]","[taskMaterials]","[description]= ' " &  
> >Forms!subformOption.materials & " ' " )
> >
> >If I use a (sub)subform based on a query instead of a DLookup, I'll need to 
> >lookup another field for another calculation, so that's not really an option 
> >either.
> >
> >Any suggestions on where else to look? This punctuation thing has got me beat.
> >
> >Thanks
> >
> >> Hi,
> >> 
> >[quoted text clipped - 24 lines]
> >> >Thanks, but I now have #Name? displayed in unitCost field. Do you have any 
> >> >other suggestions please?
> 
> -- 
> Please Rate the posting if helps you
> 
> Message posted via AccessMonster.com
> http://www.accessmonster.com/Uwe/Forums.aspx/access-forms/200709/1
> 
> 
0
Utf
9/19/2007 9:14:01 AM
Thanks. I'll split the taskMaterials table and see what happens then - if 
this was the problem, I'll post in a day or 2. Thanks again.

"AccessVandal via AccessMonster.com" wrote:

> I like to add further, if the textbox "material" and if it is blank or empty
> it will produce an error. Like what you have posted "#Error".
> 
> >kasab wrote:
> >Thanks again for staying with me.
> 
> -- 
> Please Rate the posting if helps you
> 
> Message posted via AccessMonster.com
> http://www.accessmonster.com/Uwe/Forums.aspx/access-forms/200709/1
> 
> 
0
Utf
9/19/2007 9:36:01 AM
I think this line needs to be changed:
[Forms]![frmNewQuote]![subfrmOptions].[Form]![materials]

It should be:
[Forms]![frmNewQuote]![subfrmOptions].Form![materials]

"Form" after [subfrmOptions] should not have the square brackets, since it 
is a property and not a field or control.

"kasab" <kasab@discussions.microsoft.com> wrote in message 
news:193E6394-FFC3-42EE-998D-EDD4885EAF04@microsoft.com...
> Yes, the Name property of the control that contains the data on the 
> subform
> that is used in the DLookup is "materials" as used in the DLookup.
>
> The subform RecordSource is the quotes table that contains the field
> "materials" (text datatype).
>
> And the DLookup works fine in the (sub)form displayed in form view on its
> own.
>
> "AccessVandal via AccessMonster.com" wrote:
>
>> So, your're saying the subform RecordSource is not part of the DlookUp.
>>
>> Does the control "material" is the name of the control? Check the 
>> "material"
>> properties under the Tab "Other" and look the Name field.
>>
>> >kasab wrote:
>> >Thanks again for staying with me.
>> >
>> >If I read you correctly, materials may be missing from the subform's 
>> >fields?
>> >No. This is taken directly from another table, not a query. Also, both 
>> >fields
>> >(description in taskMaterials table and materials in subfrmOptions) and 
>> >the
>> >table have correct spelling in the DLookup.
>> >
>> >=DLookUp("[TableName].[price]","[taskMaterials]","[TableName].[description]=
>> >' " &
>> >[Forms]![frmNewQuote]![subfrmOptions].[Form]![materials] & " ' " )
>> >isn't working.
>> >
>> >Both materials and the DLookup textbox are in the subform.
>> >
>> >Also, no difference with
>> >=DLookUp("[price]","[taskMaterials]","[description]= ' " &
>> >Me!materials & " ' " ) or
>> >=DLookUp("[price]","[taskMaterials]","[description]= ' " &
>> >Forms!subformOption.materials & " ' " )
>> >
>> >If I use a (sub)subform based on a query instead of a DLookup, I'll need 
>> >to
>> >lookup another field for another calculation, so that's not really an 
>> >option
>> >either.
>> >
>> >Any suggestions on where else to look? This punctuation thing has got me 
>> >beat.
>> >
>> >Thanks
>> >
>> >> Hi,
>> >>
>> >[quoted text clipped - 24 lines]
>> >> >Thanks, but I now have #Name? displayed in unitCost field. Do you 
>> >> >have any
>> >> >other suggestions please?
>>
>> -- 
>> Please Rate the posting if helps you
>>
>> Message posted via AccessMonster.com
>> http://www.accessmonster.com/Uwe/Forums.aspx/access-forms/200709/1
>>
>> 


0
BruceM
9/19/2007 11:20:55 AM
Reply:

Similar Artilces:

Update (refresh) Subform#1 when a check box is selected on Subform
Hi, I have a main form with two subforms, one linked and one not. When I select a checkbox on Subform#1, it affects a value on Subform#2 (and changes it green). However, this only updates on Subform#1, after I select a new record on that subform. So, my question is, how can I force subform#1 to refresh after I have clicked my check box on Subform#2? Any help is always greatly appreciated. Cheers, GLT. this is in the ballpark in the checkbox's after update event docmd.requery "subform#2" the correct reference to the subform will be important for e...

dlookup with if
Access 2003 On the form called fClosure ApprovalPopUp I have a combobox called SignaturePM. The default value for SignaturePM is currently =DLookUp("[ContactPMID]","[t040Project]","[ProjectID] =" & [Forms]![fClosure]![ProjectID]) How do I edit the above DLookUP to this... =if LoginUser = PMUser then DLookUp("[ContactPMID]","[t040Project]","[ProjectID] =" & [Forms]![fClosure]![ProjectID]) if LoginUser = ManagerUser then DLookUp("[ContactPMgrID]","[t040Project]","[ProjectID] =" &a...

Subform Requery
I am trying to force a requery upon the main form activation. I want to double click to add a new contact, which is listed in the subform. On double click I have an 'add contact' form open. After filling and closing, the subforms are not listing the new contact, which tells me that the subform is not requerying. I have tried several iterations of what I have found here, some causing errors, some not, but none causing the newly added contact to show up without closing/reopening the main form. Any ideas? Try and requery the query of the main form. -- KARL DEWEY Build a little...

multiselect listbox to filter subform combobox
I have a form called f4Project. It has a subform called f4ProjUnitSub. f4ProjUnitsub is a continuous form. How can I have a ListBox called lstSiteID on the main form f4Project, and select multiple options and have the subform combobox called cboUnitID display the data filtered from the listbox. i.e. On the main form I select Site A and Site B from the listbox. The subform's combo box now shows only the Units related to Site A and Site B. Thank you in advance. -- deb Hi Deb, I've never done anything like this, but here's a guess as to how you might proceed: 1.) In ...

Dlookup
Hello. I am trying to add a dlookup like I use on my forms to pull a value from an unassociated query onto my report. Is there a way I have to structure this that is different than a form? I am just looking up one field in a query called balance. The query is StatementBalance. It does not depend on anything in the report to determine the record to pull. The query has already done the work. Thanks On Thu, 6 Dec 2007 09:23:34 -0800 (PST), nybaseball22@gmail.com wrote: > Hello. I am trying to add a dlookup like I use on my forms to pull a > value from an unassociated query onto my...

Subform Linking via Code
Hello All, I am trying to change the child/master links for a subform at the click of a button. The reason for this is to switch between current and completed projects in the subform. There is already a filter on the parent for which proved too difficult to amend. My problem occurs when I click on the button. I get a warning that tells me that the master and child properties must contain the same number of fields. Is there a way to bypass this warning so that the rest of my script can run? Here is the code I have so far: Private Sub Toggle_Current_Complete_Click() Me.PMInterfaceSub.Li...

Dlookup error
Hi, and thank you for any help. I put this section into the load event on a form: Private Sub Form_Load() ACT_TRVL = DLookup("[GTotal]", "qryTravel", "[SSN] = '" & [SSN] & "'") End Sub ACT_TRVL is connected to TblAttendance The problem I am having is that I get an error message (but it still works ok): "Object doesn't support this property or method" How do I get rid of the message or correct this. Also I have noticed that the field does not show up until I close the form and reopen. Also does this for some cont...

Subform as datasheet question
Hello, Access 2007 mdb file Using DAO in VBA module of a form to query data from SQL Server and make a local table. I would like to display the data on a subform presented in datasheet view. i.e. I'm not binding any data to the parent form . . . .don't want or need to. I do want to bind a dao recordset to the subform for display purposes. It isn't immediately apparent to me how to do this. Obviously this isn't the typical parent/child relationship with bound data. Can I do this and can some of the experts on this group provide a few pointers? Thanks in adv...

dlookup syntax
Is there anything wrong with my syntax? It showed me error highting that you have cancelled the previous operations... Private Sub cmdJobCompletion_Click() strPartNumber = Nz(DLookup("W_QtyPending", "WIPRawDetails", "JobNumber='" & Me.txtJobNumber & "' AND W_QtyPending<> 0 ")) Debug.Print strPartNumber If strPartNumber <> 0 Then MsgBox ("No job completion") End If End Sub -- Message posted via http://www.accessmonster.com...

No DLookUp
Is there a way of doing this without DLookUP or Me.TxtPath? I have the Path stored in a table which I need to do incase I have to swith paths. This way I don't have to change all of my code. Thanks DS Me.TxtPath = Nz(DLookup("BackName", "tblBackPath", "BackID=1"), "") If Len(Dir$(Forms!Form1!TxtPath)) > 1 Then Dim Test2SQL As String DoCmd.SetWarnings False Test2SQL = "UPDATE table1 IN '" & Forms!Form1!TxtPath & "' " & _ "SET table1.IDName = '" & Forms!Form1!TxtInfo &...

Form multiple subforms?
I have a issue where one table Employee Shift has two depentant tables Downtime and Scrap. I have created a form subform with Employee Shift and Downtime. I also want to include an additional subform of Scrap. Is it possible to include 2 subforms on a single form? If so, does anyone have an example? You can place as many sub-forms as you want. the question is your relation you trying to display For example Employee shift ----> phone numbers ----> DownTime ----> scrap ----> name of children The above has the employee shift...

DLookup on a query??
Hello, Just wondering if it's possible to use the DLookup function on a query instead of a table? Alan Don't worry about it. I got it to work. Thanks anyway. Alan "Alan" wrote: > Hello, > > Just wondering if it's possible to use the DLookup function on a query > instead of a table? > > Alan ...

Recreate VLookup in MS Access 2003 Form Using DLookup.....
PLEASE!!! Don’t reply back and tell me to use DLookup() unless you also provide the coding I need as well. I have been trying for weeks to use DLookup() and cannot code the query properly and I am VERY frustrated. The MS Excel VLOOKUP query I am trying to recreate in MS Access 2003 is as follows: =IF(VLOOKUP(EG2,EH:EI,2,FALSE)=B2,"Good",VLOOKUP(EG2,EH:EI,2,FALSE)) Example "Config No": MTU0301-0010 Example "Concatenated Config No_4" data: 0020000100050001000100010001000200050001000100010003000200030001000200030001000100040004000200010001000...

Copy 2 fields from subform into new form
Hi, Really do not understand VBA. I want to copy the ID fields in a subform and paste them into a new form via a command button. Many thanks in advance Charlotte On Sat, 27 Feb 2010 16:22:01 -0800, Charlie <Charlie@discussions.microsoft.com> wrote: >Hi, > >Really do not understand VBA. I want to copy the ID fields in a subform and >paste them into a new form via a command button. > >Many thanks in advance > >Charlotte You'll have to give us a bit more context, I'm afraid! Are you working on the *design* of a form? or copyin...

Opening subforms in one master form????
Hi all, Can anyone tell me how to open all all the forms in one window? So I click on a button a form open within on window? Thanks Access Help wrote: > Can anyone tell me how to open all all the forms in one window? So I click > on a button a form open within on window? If I understand your request correctly you could create a main form that has a subform control on it. Using the "OnClick" event of the appropriate Command Button on this main form you could modify the SourceObject property of that aforementioned subform control to the name of the form you want to load...

Sum a DLookup
I have a report with grouping based on RepName and it displays each reps totals in [TxtTotals]. On that report I have another text box [TxtRate] with =DLookUp("CommRate","TblCommissionRates",[TxtTotal] & " Between AmountCollectedLow And AmountCollectedHigh") This gets my CommRate for each rep from a (Unrelated table) and this works fine. But I'm trying to get the average rate in the report footer But when i use =Avg([TxtRate]) its looking for a parameter [TxtRate] How can I do this without changing my query? Thanks Arlend The aggregate function...

Creating a sum of a single subform
Greetings! I have what may be a very simple question. Unfortunately, I haven't worked in Access in several years and am now lost! I am "updating" someone else's database - from a very simple level to one designed to make life easier. My main form is client information. One of it's subforms is regarding flight information for each client. We track all the flight data as well as the dollar amount of the ticket. There are 3 fields for the dollar amounts - Company Paid, Reimbursed and Total Paid What I am trying to do is have the Total field add up the Company Pa...

Subreports and subforms
Hello, I'm in a jam and I was hoping to get some suggestions for experienced individuals. I have a form with a subform. The subform is just a list of records with checkboxes. The form has a button to produce a report. The main data is obtained based on parameters dictated from the form which filters a query. The report also has a subreport. The subreport data is based on individual records selected in the subform (from the checkboxes). I guess in short, i'm trying to embed a report within another report. Is this even possible and if not, could anyone recommend a different method? ...

Tab order between subforms on a main form....
Hello, Does anyone know where I would find a good resource on tab order? I understand how to set tab order on one form but it gets tricky when I'm trying to tab correctly from one subform to another subform. On Mon, 26 Nov 2007 15:46:04 -0800, mattieflo <mattieflo@discussions.microsoft.com> wrote: >Hello, > >Does anyone know where I would find a good resource on tab order? I >understand how to set tab order on one form but it gets tricky when I'm >trying to tab correctly from one subform to another subform. The usual problem in this situation is that once y...

Dlookup
I am trying to do a Dlookup as one of the expressions in my query but not working. Tables Source: PPO Field to Return: Type Match Criteria: [CLMS]![NETWORK] = [PPO]![NETWORK] Here is my expression: DLookUp("Type","PPO","[CLMS]![NETWORK]"="[PPO]![NETWORK]") Tried all different places to put the Double Quotes (") or the Singel Quotes(') but to no avail. Please help. Thank you. Try no quotes. Is CLMS a form? If not, how is it relevant? "Brigham Siton" <bsiton@ibatpa.com> wrote in message news:%23WIKNJCaIHA.4180@TK2...

SQL and DLookup
Can you use DLookUp in SQL? Test2SQL = "UPDATE table1 IN '" & strPath = DLookup("BackName", "tblBackPath", "BackID=1") & "' " & _ "SET table1.IDName = '" & Forms!Form1!TxtInfo & "' " & _ "WHERE table1.IDNumber = 1;" DoCmd.RunSQL (Test2SQL) Thanks DS DS wrote: > Can you use DLookUp in SQL? > > Test2SQL = "UPDATE table1 IN '" & strPath = DLookup("BackName", > "tblBackPath", "BackID=1") & "' " &...

DLookup Confusion
In the below code sample, Phase 1 works as it should. Phase 2 causes errors and I am not sure why the errors are being caused. I would appreciate another set of eyes on this. I have also been able to create Phase 2 by using recordset method (not shown) and also generate the same errors. Thank you. Msg1: The "Microsoft Access" type isn't an installed database type or doesn't support the operation you chose. Msg2: "Invalid file type" Private Sub Command0_Click() Dim dbPath1 As String Dim dbType1 As String Dim dbPath2 As String Dim dbType2 As String '**...

Matching from a form to a subform
I am currently working on a database where the user can select an outing from a form on a combo box. The outing is assigned to a particular Program (via Program name). The outing might be applicable to more than 1 program (which would then have a program name which looks like (program 1/program2) In a subform on that outing form, I have a combo box that allows the user to select members to include on that outing. I want to be able to limit the member names to only those members who have the same Program name as assigned to the Outing. I'm not clear on how to drive this in a su...

Linking subform.
Access2003 I have a subform on a main unlinked one. I want a combo box on the main form to select a group of records in the subform. I always do this from the subform data source query with the builder via an expression "Forms![DJFormMLBom]![Combo3] in the criteria of the query. For some reason this is not working this time. Please help. "Frank Martin" <fm@general.com.au> wrote in message news:eI68G4YfIHA.5904@TK2MSFTNGP06.phx.gbl... > Access2003 > > I have a subform on a main unlinked one. > > I want a combo box on the main form to > se...

Problem with DLookup
Hi there, I have a table which has the following structure from which I want to extract some information: -------------------------------------------------------------------- | LGID | LeaveGroupStYr | LeaveGroupEndYr | LeaveDays | -------------------------------------------------------------------- | 1 | 0 | 2 | 10 | | 2 | 2 | 5 | 12 | | 3 | 5 | 99 | 16 | -------------------------------------------------------------...