text box queries??

[PQTIII]

Can you use a different querie for a text box on the same form? I want to display the (countofholes) from a querie different from the one the form uses. How can I do that? I put a new text box on the form and went through the expression builder on the data tab and picked the field from the queries tab , but I keep getting #name? error when I view the form. What am I doing wrong?

Thanks
paul

 

[Remou]

You need either DCount or DLookUp, depending on what you are doing.

 

[PQTIII]

I'm just pulling a field from a different querie that counts how many records contain the word "Plugged

 

[Remou]

With Dcount, you may not need the other query. Let us say that the table is called tblHoles and the field is called HoleType, you could set the Control Source for your text box to:
[tt]=DCount("*","tblHoles","HoleType='Plugged'")[/tt]

 

[PQTIII]

Remou

Can you have multiple criteria on Dcount? If so how do you do that?

thanks
paul

 

[Remou]

Yes, you can use And Or Like etc, as you can with any WHERE clause, for example:
[tt]=DCount("*","tblHoles","HoleType='Plugged' AND NumberField=2 AND DateField=#12/26/2006#")[/tt]

 

[PQTIII]

That worked great, but once you get it how do you reference that text box value to use it is calculations?

Thanks
paul

 

[Remou]

You should be able to refer to the text box by name. Let's call it txtTextbox.

In another text box (can be created with the expression builder wizard):
[tt]=[txtTextBox] + 20[/tt]

In code:
[tt]MsgBox Me.txtTextbox
Me.txtTextbox2 = txtTextbox + 20[/tt]

 

[PQTIII]

When use the testbox "totlbs1" the Dlookup works fine, but when I use it in another textbox "calc" for a calculation it gives a #Name? error.

"totlbs1" =DLookUp("SumOflbs1","Blast Product","[shotid]=[Forms]![NewMain]![shotid] and [pattype]=[Forms]![NewMain]![pattype]")

"calc" =[totlbs1]*1.34

DO I need to do something different???

Thanks
Paul

 

[Remou]

Is this just rough typing (?):
[tt]=DLookUp("SumOflbs1","Blast Product","[shotid]=[Forms]![NewMain]![shotid] and [pattype]=[Forms]![NewMain]![pattype]")[/tt]
Because it should be:
[tt]=DLookUp("SumOflbs1","Blast Product","[shotid]= " & [Forms]![NewMain]![shotid] & " and [pattype] = '" & [Forms]![NewMain]![pattype] & "'")[/tt]
Assuming that patttype is text and shotid is numeric.

The calc textbox seems fine. Have you tried using the expression builder, to make sure that the names and so on are exactly right?

 

[PQTIII]

I copied your statement and it brought up the same number mine did. I used the expression builder and picked the textbox for the calculation and I still got the #Name? error. Am I doing something else wrong?

Thanks
Paul

 

[Remou]

What happens with a message box? Say you put a message box in some click event:
[tt]MsgBox Me.[totlbs1]*1.34[/tt]

I can't seem to see where the problem could be. I tried a 'calc' textbox with Dlookup on a test form, and it seemed to work ok.