UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> How To Make A Hyperlink To A Form That Is A Query, Access 2016    
 
   
lcrews
post Oct 19 2017, 02:02 PM
Post#1



Posts: 46
Joined: 22-August 17
From: Los Angeles


I would like to make a hyperlink to a form whose record source is a query. The form opens blank right now and the user must search for a parameter + re-query. I'd like the form to open up with that parameter already keyed in and re-queried. For instance, I want the user to click a hyperlink from text "Make changes here" to open form2 with information matching Txt1 from form1. I tried using this script to no avail:
CODE
Private Sub Label237_Click()
    Const cstrForm As String = "frm_ItemMaster"
    DoCmd.OpenForm cstrForm, WhereCondition:="[Item No]=" & Me.ItemNumber
End Sub
Go to the top of the page
 
doctor9
post Oct 19 2017, 02:45 PM
Post#2


UtterAccess Editor
Posts: 17,924
Joined: 29-March 05
From: Wisconsin


lcrews,

This looks like it should work. Is ItemNumber a text field? If so, you should try this:

CODE
DoCmd.OpenForm "frm_ItemMaster", WhereCondition:="[Item No]=""" & Me.ItemNumber & """"


By the way, you might want to rename "Label237". Also, 237 is a pretty high number for a form control. What does this form look like??

Hope this helps,

Dennis

--------------------
(;,;) Li'l Cthulu says: Please talk about what you're trying to do, as well as how you're doing it.
Changing your real table name to "Table1" and your real form name to "Form1" in your posts makes it more difficult to understand what's going on, not easier.
Guidelines for Posting Questions
Go to the top of the page
 
lcrews
post Oct 19 2017, 03:04 PM
Post#3



Posts: 46
Joined: 22-August 17
From: Los Angeles


Dennis,
Here's a screenshot of my form. I did try your suggestion already. The problem is frm_ItemMaster opens blank to begin with. So when I use this script, the form opens blank and the filter property is set to [Item No]=A475AD (which is what's in txt1). The form is still blank though.
Attached File(s)
Attached File  Screenshot.png ( 36.82K )Number of downloads: 18
 
Go to the top of the page
 
lcrews
post Oct 19 2017, 03:08 PM
Post#4



Posts: 46
Joined: 22-August 17
From: Los Angeles


I should mention the reason that I am searching for this solution.

Currently the form is centered on tbl_MBR where field1 is the filter. I added fields from tbl_itemmaster that are related to the record being displayed. I believe that, since there are multiple tables in the reference, I cannot edit the fields from tbl_itemmaster.

If that's not the case and all I need to do is adjust a setting to be able to make changes to these fields, please let me know.
Go to the top of the page
 
doctor9
post Oct 19 2017, 03:10 PM
Post#5


UtterAccess Editor
Posts: 17,924
Joined: 29-March 05
From: Wisconsin


lcrews,

> I did try your suggestion already.
Do you know the datatype of Item No? Does it really have a space in the field name?

> The problem is frm_ItemMaster opens blank to begin with.
This code works best with a form that opens showing all of the records to begin with. Are you saying the form is unbound? If not, why does it open blank to begin with?

Hope this helps,

Dennis

--------------------
(;,;) Li'l Cthulu says: Please talk about what you're trying to do, as well as how you're doing it.
Changing your real table name to "Table1" and your real form name to "Form1" in your posts makes it more difficult to understand what's going on, not easier.
Guidelines for Posting Questions
Go to the top of the page
 
lcrews
post Oct 19 2017, 03:17 PM
Post#6



Posts: 46
Joined: 22-August 17
From: Los Angeles


It opens blank to begin with so that the user doesn't see a record they aren't looking for. Here is what the other form looks like. The user selects a record from the combo box (top left) and then it shows the record. I understand what you are saying about the form needing to display all records for the filter to work properly. Maybe I should ask instead, how to make these fields editable. I had a updateable query set up, but my audit trail module wasn't working on the subform level. Since I got it offline, and I'm not that great with Access VB, I assumed that the fields need to be at the main form level. Is that wrong?
Attached File(s)
Attached File  Screenshot.png ( 41.29K )Number of downloads: 10
 
Go to the top of the page
 
doctor9
post Oct 19 2017, 03:35 PM
Post#7


UtterAccess Editor
Posts: 17,924
Joined: 29-March 05
From: Wisconsin


lcrews,

> It opens blank to begin with so that the user doesn't see a record they aren't looking for.

Does this form open in any other way besides clicking this label?

A picture of your form doesn't really help in describing why the form opens without showing any records, unfortunately. I'm only seeing a couple of dozen controls in this image. Not sure how you got over 200, unless you've deleted a lot of them.

Any audit trail software you're using should not be triggered when the form opens - logically it should only be triggered by changing data, deleting data or inserting data.

Hope this helps,

Dennis

--------------------
(;,;) Li'l Cthulu says: Please talk about what you're trying to do, as well as how you're doing it.
Changing your real table name to "Table1" and your real form name to "Form1" in your posts makes it more difficult to understand what's going on, not easier.
Guidelines for Posting Questions
Go to the top of the page
 
lcrews
post Oct 19 2017, 03:50 PM
Post#8



Posts: 46
Joined: 22-August 17
From: Los Angeles


Dennis,

I attached my database so you can see more accurately what I'm working with. From the main menu I want the user to go to "Item Master" and input the information for a particular item "A475AD". Now when they're done they should go back to the main menu and move to "MBR Process". For example, CO# "C20709" and Item "A475AD", selecting "View MBR", will show all items from tbl_MBR and tbl_ItemMaster that are related. If they don't like the info presented, I need for them to be able to change it. However, using the query method (updateable) wasn't being tracked by the audit trail (located in module 1). So I added the fields directly to frm_ViewMBR instead, but they are not updateable.
Attached File(s)
Attached File  Database1.zip ( 964.95K )Number of downloads: 19
 
Go to the top of the page
 
doctor9
post Oct 19 2017, 03:58 PM
Post#9


UtterAccess Editor
Posts: 17,924
Joined: 29-March 05
From: Wisconsin


lcrews,

Your attachment depends on tables that are not included in the file. Also, it has dozens of objects that likely don't apply to this problem.

Can you please just answer my question?

Dennis

--------------------
(;,;) Li'l Cthulu says: Please talk about what you're trying to do, as well as how you're doing it.
Changing your real table name to "Table1" and your real form name to "Form1" in your posts makes it more difficult to understand what's going on, not easier.
Guidelines for Posting Questions
Go to the top of the page
 
lcrews
post Oct 19 2017, 04:05 PM
Post#10



Posts: 46
Joined: 22-August 17
From: Los Angeles


Sorry Dennis,

Yes the form opens another way. It opens from the main menu. The user should enter "Item Master" first to enter the details. If another user comes in and is ready to access the MBR they go to "MBR Process" etc. If they don't like the values in those specific fields on the "Info Tab", that's why I want the link to go back to "Item Master" - but I want the information for "A475AD" (What's in Txt1 on frm_ViewMBR) to be displayed.

Again, sorry for the database flaw. I forgot that I split the database. I forgot to zip the tables.

Landon
Go to the top of the page
 
doctor9
post Oct 19 2017, 04:12 PM
Post#11


UtterAccess Editor
Posts: 17,924
Joined: 29-March 05
From: Wisconsin


Landon,

Okay, so if the form opens two different ways, I'm assuming you want the form to display no records when the user opens the form from the main menu? If so, why would you do that?

To be clear, I'm asking these questions because I don't work for your company and don't understand the process at all. If that form needs to open and display no records, what will the user do next?

Dennis

--------------------
(;,;) Li'l Cthulu says: Please talk about what you're trying to do, as well as how you're doing it.
Changing your real table name to "Table1" and your real form name to "Form1" in your posts makes it more difficult to understand what's going on, not easier.
Guidelines for Posting Questions
Go to the top of the page
 
lcrews
post Oct 19 2017, 04:18 PM
Post#12



Posts: 46
Joined: 22-August 17
From: Los Angeles


Dennis,

The form doesn't HAVE to open that way. I set it up that way because I thought seeing all records would confuse the user before they have the chance to search for an item. I think what I'll do is duplicate this form, have the hyperlink take the user here. That way I can have one form that opens blank, and one that opens with all records so that the filter works. I'll try that and let you know either way. Thank you so much for your patience. Is there a way to make the form fields updateable instead without using a query?

Landon

Update- Works like a charm
Go to the top of the page
 
doctor9
post Oct 19 2017, 04:27 PM
Post#13


UtterAccess Editor
Posts: 17,924
Joined: 29-March 05
From: Wisconsin


Landon,

If you want the form to open empty from the main menu, just filter it in a way that you know it will return no records.
CODE
DoCmd.OpenForm "frm_ItemMaster", WhereCondition:="[Item No]=0

Making an extra copy of the form means you need to update BOTH forms when you make a change. Then, when the combobox's AfterUpdate event changes the WhereCondition, the users see the data.

For example,on one of my forms that lists MSDS sheets, the user sees 1100 MSDS sheets when they open it. The user knows that the name of the material contains the word "Asphalt" but they're not sure if there are other words like "Hot Mix Asphalt", so they filter the form to records where the name of the material contains the word "Asphalt". At that point they're down to 55 records, and they can look for other things to filter, or just cycle through the records to find the one they're looking for.

> Is there a way to make the form fields updateable instead without using a query?
I'm not sure what you're asking here. As long as the query is updateable, and the form controls aren't locked or disabled, the user should be able to update the data. You can also bind a form to just a table without a query, if that's a possibility. I feel like this question has more to do with your issues understanding your audit trail subroutines rather than just updateable form controls. (Remember, forms have controls. Tables have fields.)

Hope this helps,

Dennis

--------------------
(;,;) Li'l Cthulu says: Please talk about what you're trying to do, as well as how you're doing it.
Changing your real table name to "Table1" and your real form name to "Form1" in your posts makes it more difficult to understand what's going on, not easier.
Guidelines for Posting Questions
Go to the top of the page
 
lcrews
post Oct 19 2017, 04:30 PM
Post#14



Posts: 46
Joined: 22-August 17
From: Los Angeles


Man Dennis,

Thank you for explaining these things in a common sense way. I understand it. Now I don't have to duplicate the form. As for making the fields updateable, you're right that I need to understand more about the audit trail subroutines first. Thanks again for the help! I really appreciate it.

Landon
Go to the top of the page
 
doctor9
post Oct 19 2017, 04:37 PM
Post#15


UtterAccess Editor
Posts: 17,924
Joined: 29-March 05
From: Wisconsin


Landon,

Hope that helped a bit. If you don't understand your audit trail code, you might want to consider using another method for doing the audit trail - preferably one that's easy to understand. There are several demos in our Code Archive, just search using the keyword "audit".

As I said before, an Audit Trail (at least in my mind) just tracks those three things:

1. When the user adds a new record
2. When the user deletes a record
3. When the user changes a record

So, you can attach code to a form's BeforeUpdate, and BeforeDelete events (as well as individual controls' BeforeUpdate events, if necessary) and write data to your audit trail table based on what your code sees.

But none of this should affect which records a user SEES, though. It should just react to what they DO with the form/data.

Hope that clears things up. Bottom line: if you're the person responsible for maintaining the database, it's in your best interest to understand how it all works. Don't just download/copy code and hope. Read it, and understand it as best you can. Add comments to remind yourself of what's happening and why, if necessary.

Dennis

--------------------
(;,;) Li'l Cthulu says: Please talk about what you're trying to do, as well as how you're doing it.
Changing your real table name to "Table1" and your real form name to "Form1" in your posts makes it more difficult to understand what's going on, not easier.
Guidelines for Posting Questions
Go to the top of the page
 


Custom Search
RSSSearch   Top   Lo-Fi    16th December 2017 - 07:54 AM