code to set the value of a query as the value in a control

[Medlar]

I have a database of clients each with a unique reference number. Each event for that client also has a unique reference number.
My main client for is based on a query that requires the client number as a parameter to return the correct client details.
If I get a payment from say client Smith and they dont quote the invoice number I have another tabular form based on a query that is set with parameter client name and returns all the Smiths with key additional data (second name, date of birth, address etc) that allows me so I can find the correct Smith and their client number.
Elsewhere in my database I have Private subs in On Click in controls in tabular forms based directly on tables that open the relevant record for the control eg event number opens the relevant event form.
The same Code in On Click in my form that returns all the Smiths still requires me to enter the parameter ie Client number to open the client form because client form is based on a query. Is there a simple way of making my code in the client number control use the client number I click on and enter it automatically into the query for the client form?
Sorry this is so long winded but I was trying to make my problem clear LOL
This is an example of my code from another place in my database:

Private Sub Event_no_Click()
On Error GoTo Err_Event_no_Click

    Dim stDocName As String
    Dim stLinkCriteria As String

    stDocName = "Events new"
    
    stLinkCriteria = "[Event no]=" & Me![Event no]
    DoCmd.OpenForm stDocName, , , stLinkCriteria

Exit_Event_no_Click:
    Exit Sub

Err_Event_no_Click:
    MsgBox Err.Description
    Resume Exit_Event_no_Click
End Sub

[NeoPa]

Sorry for all the edits Medlar. My system crashed half way through (Power Outage) and the edit interface on here is still a little clumsy with all the updates going through recently. Every time I tried to add some extra explanation on a new line it fired off the edit instead.

Anyway, your post is now fixed and the last edit comment is the relevant one.

[NeoPa]

Medlar, you appear to have the concept already.

In the first part you determine the correct ID for the client, then you use code very similar to what you already have, to open a new form with the specified stLinkCriteria.

If you're still having problems after that, then you need to let us know what those problems are more specifically.

[Medlar]

Thanks for your reply
I am rather new to this site but not sure what you mean by multiple edits?
But the problem I seem to have is that the query on which my client form is based has the parameter 'Enter Client number' which of course works fine.
But if I want to look up the Smiths for example to find the client number for a particular Smith using another form based on a different query my code in the client number control 'On Click' does not by pass the Message box from the client form query asking me to enter the client number - hope that makes sense. In essence I am looking for a way to have code that enters the client number I have clicked on.

[NeoPa]

Medlar:
I am rather new to this site but not sure what you mean by multiple edits?

I edited your post a number of times. That was mainly down to the clumsiness of the interface, but somewhat down to my clumsiness too (and the power outage). When your post is edited you should receive an e-mail explaining this. I assumed you would have received one for each edit, which would have been three or four, so I felt it reasonable to explain and apologise for the inconvenience. The emails are automatic and not something I can control manually by the way.

[NeoPa]

As to your question, that is what I understood you to mean, and that is exactly what your example code does for you, albeit maybe with different control and/or field names. Once you select the client you require and click on the Command Button, the code executes and opens the new form filtered to exactly the record you selected. That's what that code does.

The only changes you need to make are to the names of the items. That's not something I can even help with at this stage as you have not shared any of that information with us.

[John Buxton]

Thanks for your reply.
I know the code works the issue seems to be with me setting a criteria in my query which asks for the client number. This works when I open my clients form from a button on my switchboard ie it asks me for the client number to find the correct record.
The issue is when I use the form returning the list of for example Smith's. When the code is in the client number control on this form it works fine but the query on which the clients form is based still asks me to enter the number again and what I was asking is is there a way to suppress this message in this instance or programatically enter the number.
I do have a work round by using the find client number query to ask for either client number or surname.
I guess I am trying to be too picky in wanting to find a way to achieve my preferred solution LOL.
Anyway thanks very much for looking at this for me.
Cheers

[NeoPa]

Let me try to deal with the various points separately. I'll start with procedural ones :

1. If you're a member, please post as that member. Posting with your name in some cases but using your account name in others is simply confusing.
2. If you have a new question, then please post it in a new thread. We try to keep one question per thread on this site, for what I would hope were obvious reasons. Posting a link in one thread to your new thread, or even posting a link in the new to make readers aware of the context from the old, is quite happily permissable, but posting multiple questions in the same thread is not helpful.
   
   I will split this off for you, but would be grateful if you'd remember for any future occasions.

As for avoiding the prompt occurring from a parameterised query (such as yours) this is only possible when using Recordset code. I so wish that it had been made possible to handle for the RecordSources of objects, as I have had many occasions where I could have used it otherwise, but I believe they omitted that from the functionality for some reason. I'm not so naive to assume that just because I don't see the rationale of this, that therefore there was none, but I have nevertheless not seen it myself. It appears to me that it would make life smoother. That's all I can say really.

What I normally do now though, is either of :

1. Create a form where the parameter can be found in one of its controls, and make the query refer to that control directly.
2. Create a form where the parameter can be entered within a control, but create SQL dynamically within code attached to the form which is based on the query and the literal value entered. This version can be more flexible as it can handle the conditions where data isn't entered also.

[NeoPa]

A new thread has been created (Close Form after New Form Opened) from a post added in here. Any interested parties can follow the link to find it.