Linking button on form to report

[robertdudley95]

I'm creating a database for my business, and it's pretty much finished apart from a few annoying little problems eating away at me.



One of them is that I want to search through quotes and invoices in a form (which has been easily achieved) and then allow the end user (i.e. me and my colleague) to click the quote/invoice number and return the quote/invoice for that exact job.

How would I go about doing this?

Below is an example of my "Quote Wizard" so far. It uses a query which allows me to search for the surname of the customer before entering the form, and includes a subform in which I can enter the quote details.

Attachment 5068

As you can see, I have included a 'Prepare for printing' button, but I want to get rid of this as this uses the same query as the form, and I would rather the form just direct me to the quote I need straight away without me having to type anything in.

How would I go about doing this?

[June7]

Several ways to do this. Requires code. I use only VBA.

1. apply a filter to the form

2. move to the record

3. show results in a listbox

4. open another form or a report that is filtered to show only that record. WHERE CONDITION argument of OpenForm (or OpenReport) will pass criteria to the object.
DoCmd.OpenForm "form name", , , "ID=" & Me.ID
DoCmd.OpenReport "report name", , , "ID=" & Me.ID

Which appeals to you?

Is this a form/subform arrangement?

[robertdudley95]

Hi June7,


First of all - yes, this is a form/subform arrangement. All of the forms have exactly the same GUI, and all that changes is the subform since they are all linked by relationships.

Secondly, I want the actual form field to link to the report. Would I use exactly the same method as you've stated in the message? I'll also want to rename the caption (which I already know how to do) and change the text in each of them to "[...]", which is a sufficient hyperlink, thus stopping the end user from having to type in the quote number every time that they want to use the query. Also, having the hyperlink to the exact quote eliminates the need to have the Quote ID in the form anyway.

Finally, how would I go about applying these pieces of code to the form. I'm only a novice at the minute, and I haven't got onto the coding side of things yet.

Thanks,

Robert

[hertfordkc]

Several ways to do this. Requires code. I use only VBA.

1. apply a filter to the form

2. move to the record

3. show results in a listbox

4. open another form or a report that is filtered to show only that record. WHERE CONDITION argument of OpenForm (or OpenReport) will pass criteria to the object.
DoCmd.OpenForm "form name", , , "ID=" & Me.ID
DoCmd.OpenReport "report name", , , "ID=" & Me.ID

Which appeals to you?

Is this a form/subform arrangement?

__________________________________________________ _______________
into a list box. Once you have achieved that, you can execute the code which June7 suggested in an event tied to the list box. If the user is clicking on a specific field in the list box, then use the onclick event. Otherwise, maybe oncurrent??
This is a very simple example which illustrates using VBA with controls.
https://www.accessforums.net/attachm...2&d=1319831849

[robertdudley95]

Hi hertfordkc,

I have one problem with this - as you can see in the screenshot below, I don't want to link the quotes with a list box, as the subform creates and links to them in one.

Attachment 5072

What I've done here is used the code that June7 suggested, which is:

Private Sub Quote_ID_Click()
    DoCmd.OpenReport "rptQuote", , , Me.Quote_ID.Text
End Sub

but what's happening now is that it's giving me every single quote in one quote, and I also want to change the text of the Quote ID field from the actual quote number to "[...]".

How do I do this?

[robertdudley95]

OK, so it doesn't matter now. I changed the name of the Quote ID field to QuoteID and substituted it into the line of code, and now it works perfectly apart from the fact that I want to change the name of the text.

[hertfordkc]

Glad it is working.
I interpret your last comment to mean that you want to change the contents of a label associated with a text box. In design mode, highlight the label you want to change and in the properties you can change the caption.

[robertdudley95]

Glad it is working.
I interpret your last comment to mean that you want to change the contents of a label associated with a text box. In design mode, highlight the label you want to change and in the properties you can change the caption.

Actually, I want to hide the quote number itself and replace it with the [...] identifier. Can this actually be done?

[hertfordkc]

Sorry I'm so slow.
I now read that you really don't want the quote number displayed. If you built the subform by hand based on a query, you can make the fields invisible by going to design view and editing those controls. If you used a form wizard, I'm not sure how to get rid of the Quote ID unless you can make it invisible in the query???
I don't understand your reference to the [...] identifier. Can you elaborate?

[robertdudley95]

Sorry I'm so slow.
I now read that you really don't want the quote number displayed. If you built the subform by hand based on a query, you can make the fields invisible by going to design view and editing those controls. If you used a form wizard, I'm not sure how to get rid of the Quote ID unless you can make it invisible in the query???
I don't understand your reference to the [...] identifier. Can you elaborate?

Firstly - don't worry about being slow.

Secondly - the [...] identifier is something that is used in Microsoft example databases when linking a field to a form/report. However, since the databases are created by Microsoft I can't enter Design View whatsoever.

If there is no way of adding this identifier to the form, then it isn't the end of the world...

[hertfordkc]

<<<<Secondly - the [...] identifier is something that is used in Microsoft example databases when linking a field to a form/report. However, since the databases are created by Microsoft I can't enter Design View whatsoever.>>>>
------------------------------------------------------------------------------------------------------------------------------------
Tell me what is wrong here:
you have your database open
you can see the navigation pane
you can see the form name that you have been altering
you can select the form and right click which brings up a menu
In the menu, you can select design view
you can now see the underlying controls and control names on the form
you can click on show properties and open a property form
you can click on an individual control on the form, and you can see its property sheet
you can see a control source or a bunch of event property lines
you can click on one of the event property lines
you can see some symbols at the right including the [...]
you can click on the [...] and a menu appears
you can select "code"
you now see a visual basic window with an empty sub
you are now free to rule the world

[robertdudley95]

<<<<Secondly - the [...] identifier is something that is used in Microsoft example databases when linking a field to a form/report. However, since the databases are created by Microsoft I can't enter Design View whatsoever.>>>>
------------------------------------------------------------------------------------------------------------------------------------
Tell me what is wrong here:
you have your database open
you can see the navigation pane
you can see the form name that you have been altering
you can select the form and right click which brings up a menu
In the menu, you can select design view
you can now see the underlying controls and control names on the form
you can click on show properties and open a property form
you can click on an individual control on the form, and you can see its property sheet
you can see a control source or a bunch of event property lines
you can click on one of the event property lines
you can see some symbols at the right including the [...]
you can click on the [...] and a menu appears
you can select "code"
you now see a visual basic window with an empty sub
you are now free to rule the world

OK, so I've sort of mastered this now. If I create a new form field, it'll give me the option to set the [...] identifier, and type in exactly the same piece of code as before, but now it's telling me that I can't use that field, and that I have to use the QuoteID form field. Any way round?

[June7]

I am not able to make sense of this, do you want to provide project for analysis? Make copy, remove confidential data, run Compact & Repair, zip if still large, attach to post.

Your code is incomplete
DoCmd.OpenReport "rptQuote", , , "Quote_ID=" & Me.Quote_ID

[hertfordkc]

<<<OK, so I've sort of mastered this now. If I create a new form field, it'll give me the option to set the [...] identifier, and type in exactly the same piece of code as before, but now it's telling me that I can't use that field, and that I have to use the QuoteID form field. Any way round?>>>
----------------------------------------------------------------------------------------------------------------------------------------------------
You're creating a new control on a form (fields belong to tables and queries) and you want to change its label and control source. In design mode, the label might have a name like "label19" and some sort of caption, perhaps "Label19". You can refer to "label19" in VBA and change its properties. Same thing for a new control, say "textbox19". The one item you almost always need to set if you put a new control on a form is its control source, i.e. where does it get its data. If the control is in a subform and you want a value from the main form, you have to be careful with your syntax. It will be something like Forms!mainfrmname!!maincntrlname.[value|property]. Don't trust me on this. Look it up.
Looking at your form just doesn't give enough information to put it all together.
As June7 said, we'll take a look if you post your DB.

[robertdudley95]

I am not able to make sense of this, do you want to provide project for analysis? Make copy, remove confidential data, run Compact & Repair, zip if still large, attach to post.

Your code is incomplete
DoCmd.OpenReport "rptQuote", , , "Quote_ID=" & Me.Quote_ID

Yeah, sorry - I forgot to quote the whole code...

Anyway, here is the database (attached as a zip file) - I've put a couple of example records in. And ignore the login screen - it's a feature I'm currently working on. The form that you need is called frmQuoteWizard. You'll have to enter a customer surname (either King or Didcot) if you don't want to go into Design View straight away.

Attachment 5083