Event procedure for command button

[moori]

Hello everyone,

I have a query that I want to take its criteria from a text box (which is a form).
The user shall put data into the box, hit the "calculate" button and get his result.
The query works just fine when I enter my data in the box and open the query "manually". On pressing the button, however, the query window opens, but is empty. So something must be wrong with my event procedure.

This is the query "qryPriceCalculator":
SELECT...
FROM...
WHERE (((Clients.CNumber)=[Forms]![frmPriceCalculator]![boxCNumber]) AND ((Products.PNumber)=[Forms]![frmPriceCalculator]![boxPNumber].[Text]));

And this is the event procedure:
Private Sub CmdCalc_Click()
DoCmd.OpenQuery "qryPriceCalculator"
End Sub

As I said, this opens the query, but doesn´t give a result.
I also tried running the query directly in VBA, but I can´t get the reference to the text boxes right (CNumber is numeric, PNumber is a text string):

Private Sub CmdCalc_Click()
Dim strSQL As String

strSQL = "SELECT... FROM... WHERE (((Clients.CNumber)=Me.boxCNumber.value) AND ((Products.PNumber)='Me.boxPNumber.value'));"

DoCmd.OpenQuery strSQL

End Sub


Does anyone have a clue what´s wrong with my procedure? Or can help me out with the latter statement?

Thanks a lot,
moori

 

[Alansidman]

((Products.PNumber)=[Forms]![frmPriceCalculator]![boxPNumber].[Text]))

Looks like there is an extra object or control in this statement. Does this refer to a control within a subform? If so, then the syntax should be as follows

Forms!MainformName!subFormName!Form.ControlName

Alan

 

[John Big Booty]

If Alan's guess is correct. You will find this link invaluable, when formulating the correct Syntax for referring to Sub-Forms and their properties.

 

[moori]

Thanks for your answers! But I´m afraid I don´t understand what you mean
What could be the extra control? I have many forms for all my DB tables, but this form "frmPriceCalculator" is not connected with any of them

 

[Alansidman]

The normal syntax for referring to a control on a form is

forms!FormName.Control

You have four items in your statement, not three. What does the fourth one represent or is it a typo?

Yours appears in this syntax which is probably confusing Access.

Forms!FormName.Control.Control

 

[moori]

Oh, I didn´t think this was a control! It should just tell Access that whatever is in the boxPNumber is a text and not a value/number. I chose "text" instead of "value" in the expression builder and this is what came out ;-)
But it seems like the query needs this information, because when I delete it, it doesn´t work

 

[Alansidman]

I don't think it makes a difference. You are asking Access to compare two fields (controls) and if they are the same then execute. Are they not formatted the same? If not, then that is a different issue as you are now trying to compare apples and oranges.

 

[moori]

But how can I format a text box entry? I can not choose if it´s a text or a number in the properties settings.
And the query itself is already running with these data, just the VBA command is not

 

[Alansidman]

I think its time for you to post a copy of your database with sample data (nothing confidential) as I am not understanding what you are now saying. I am very confused. Be sure to run a compact and repair. To get the maximum visibility, if your db is not already in a 2003 format or earlier, do so as many on this forum have not upgraded and you would not want to exclude their expertise.

Alan

 

[moori]

Ok, I will! But this requires some research for me (did I mention I am an Access Dummie? ;-)) and will take some time! Thanks so far for your efforts!

 

[John Big Booty]

You will find the Compact and Repair facility under the Tools menu, Database Utilities.

 

[Alansidman]

this should help. It is from the forum FAQ

How do I attach a file to a post?

To attach a file to your post, you need to be using the main 'New Post' or 'New Thread' page and not 'Quick Reply'. To use the main 'New Post' page, click the 'Post Reply' button in the relevant thread.

On this page, below the message box, you will find a button labelled 'Manage Attachments'. Clicking this button will open a new window for uploading attachments. You can upload an attachment either from your computer or from another URL by using the appropriate box on this page. Alternatively you can click the Attachment Icon to open this page.

To upload a file from your computer, click the 'Browse' button and locate the file. To upload a file from another URL, enter the full URL for the file in the second box on this page. Once you have completed one of the boxes, click 'Upload'.

Once the upload is completed the file name will appear below the input boxes in this window. You can then close the window to return to the new post screen.

What files types can I use? How large can attachments be?

In the attachment window you will find a list of the allowed file types and their maximum sizes. Files that are larger than these sizes will be rejected. There may also be an overall quota limit to the number of attachments you can post to the board.

How do I add an image to a post?

If you have uploaded an image as an attachment, you can click the arrow next to the 'Attachment Icon' and select it from the list. This will be inserted into your post and can be located where you want it displayed.

To include an image that is not uploaded as an attachment and is located on another website, you can do so by copying the full URL to the image, (not the page on which the image is located), and either pressing the 'Insert Image' icon or by typing

after it, ensuring that you do not have any spaces before or after the URL of the image. You can insert pictures from your albums (?) in this way too.

 

[vbaInet]

Whilst you're in the process of uploading your db, this is what your SQL statement should look like:

SELECT [COLOR=Red]...[/COLOR]
FROM [COLOR=Red]...[/COLOR]
WHERE (((Clients.CNumber)=[Forms]![frmPriceCalculator]![boxCNumber]) AND ((Products.PNumber)=[Forms]![frmPriceCalculator]![boxPNumber]));

I hope the elipsis (i.e. ...) actually isn't what you have in your query? I would doubt that because the query won't compile in the first place.

Add both references to the Parameters list and choose the appropriate type for each of them. You can open up the Parameters list using the Parameters button in the toolbar or ribbon of the query.

 

[moori]

Oh my god, vbaInet, this worked out!!! :-D
I didn´t know about the parameter list...
If anyone is still interested in seeing the database I will upload it gladly, but my problem is solved!
THANKS SO MUCH, EVERYONE!
See you ;-)

 

[vbaInet]

Glad we could help.

Alan was trying to tell you that the .[Text] part was not needed. You only use that in a module or Expression Builder in events like the Got Focus, Lost Focus or Change events (for example). Queries don't need .Value or .Text.