473,324 Members | 2,417 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,324 software developers and data experts.

Dbl click a form field to open another form based on value

I found this thread and its close to what I need (http://bytes.com/topic/access/answers/208888-open-form-based-field-entry) but doesnt completely answer my issue. I have a field on a form (subfrm_xmit_docs.Path) which, when dbl clicked should open a different form and go to the record that correspondes to the value in the field that was clicked on. The field in the 2nd form is frm_hardcopies.Filename. Also, using the process, is there a way to prevent an error if the field being dbl clicked is empty, or it just doesnt load the 2nd form at all if null?
Oct 6 '11 #1

✓ answered by patjones

This is a question of defining your filter properly, which I don't think you've done. When you define a filter, it has to look just like the WHERE clause of a SQL statement. For instance:

Expand|Select|Wrap|Line Numbers
  1. stLinkCriteria = "Filename = '" & Me.Path & "'"

This tells Access to open the second form and populate it with the record where Filename is equal to the value in your path control. The value for Me.Path must be enclosed in ' ' since it's a string, which is the reason for the concatenation.

If this works for you then we can address null values.

11 4145
patjones
931 Expert 512MB
What code do you have so far?

Pat
Oct 6 '11 #2
I have this attached to the dblclick event, but it doesnt open frm_hardcopies and jump to the correct record. It just opens the form and goes to a new record.

Expand|Select|Wrap|Line Numbers
  1. Private Sub Path_DblClick(Cancel As Integer)
  2.  
  3. On Error GoTo Err_Path_Click
  4.  
  5.     Dim stDocName As String
  6.     Dim stLinkCriteria As String
  7.  
  8.     stDocName = "frm_hardcopies"
  9.     DoCmd.OpenForm stDocName, , , stLinkCriteria
  10.  
  11. Exit_Path_Click:
  12.     Exit Sub
  13.  
  14. Err_Path_Click:
  15.     MsgBox Err.Description
  16.     Resume Exit_Path_Click
  17.  
  18. End Sub
Oct 6 '11 #3
patjones
931 Expert 512MB
Given the code that you've posted, I would not expect the form to do anything other than open up blank. When a form opens up, you need to tell it where to get its data from. For a bound form, this usually means setting the form's recordsource property to the proper table name, query name, or SQL string in the Form Open event.

You can filter the form's recordsource and thereby get to a particular record or records by using the OpenForm Filter argument, or even the OpenArgs argument, but you have not assigned anything to stLinkCriteria...so as you have posted it, the filter is not going to do anything.

Pat
Oct 6 '11 #4
beacon
579 512MB
You haven't set a value for the stLinkCriteria variable, so at best the form will open, but nothing will display. However, this is likely causing an error.

You'd need to add something like:
Expand|Select|Wrap|Line Numbers
  1. Dim stLinkCriteria As String
  2.  
  3. stLinkCriteria = Me.Path.Value 'Assuming that the value you want is in the Path field
  4.  
If you want to make sure there's a value in the field, you can do this in the same subroutine by using the following after assigning the value of Path to the variable:
Expand|Select|Wrap|Line Numbers
  1. If (IsNull(stLinkCriteria)) Or (stLinkCriteria = "") Then
  2.      MsgBox "A value must be entered to launch the new form"
  3.      Cancel = True
  4. End If
  5.  
Then you can use DoCmd.OpenForm to open the form because it will have passed the test to determine if a value was entered in the field.

Hope this helps,
beacon
Oct 6 '11 #5
ok, what if I wanted to have it do something like...

when I double click the Path field on a filename where that value isnt currently a record that appears in the table linked to frm_hardcopies, it asks "There is no record associated with this filename. Do you want to create one?" Click yes, and it copies the value from subfrm_xmit_docs.path to frm_hardcopies.filename in a new record.

Also, when I use the current code with your addition for the stLinkCriteria, I get the error "Syntax error (missing operator) in query expression. Here is what I have right now

Expand|Select|Wrap|Line Numbers
  1. Private Sub Path_DblClick(Cancel As Integer)
  2.  
  3. On Error GoTo Err_Path_Click
  4.  
  5.     Dim stDocName As String
  6.     Dim stLinkCriteria As String
  7.     stLinkCriteria = Me.Path.Value 'Assuming that the value you want is in the Path field
  8.  
  9.  
  10.     stDocName = "frm_hardcopies"
  11.     DoCmd.OpenForm stDocName, , , stLinkCriteria
  12.  
  13. Exit_Path_Click:
  14.     Exit Sub
  15.  
  16. Err_Path_Click:
  17.     MsgBox Err.Description
  18.     Resume Exit_Path_Click
  19.  
  20. End Sub
Oct 6 '11 #6
patjones
931 Expert 512MB
You're a little all over the place here, and not giving us the right information that we need in order to answer your question.

What exactly is Me.Path.Value? If you run Debug.Print for it, what does it show you? What is the form's recordsource? What line of code does the error occur at?

I'm asking these questions because the error is arising either from a problem with Me.Path.Value, or a problem with the second form's recordsource.
Oct 6 '11 #7
ok, Me.Path.Value is the Path field in subfrm_xmit_docs. the field that it should link to on the other form is frm_hardcopies.Filename

the goal is: if I double click on the field "Path" in a record in subfrm_xmit_docs where the value is XYZ, I want frm_hardcopies to load where "Filename" is equal to XYZ. Its a jump to. I dont want to make field "Filename" in frm_hardcopies something like =Forms!subfrm_xmit_docs!Path , since it is a normal form used for data entry and Filename has a source already.

frm_hardcopies is linked to tbl_hardcopies
subfrm_xmit_docs is linked to tbl_xmit_docs
Oct 6 '11 #8
patjones
931 Expert 512MB
This is a question of defining your filter properly, which I don't think you've done. When you define a filter, it has to look just like the WHERE clause of a SQL statement. For instance:

Expand|Select|Wrap|Line Numbers
  1. stLinkCriteria = "Filename = '" & Me.Path & "'"

This tells Access to open the second form and populate it with the record where Filename is equal to the value in your path control. The value for Me.Path must be enclosed in ' ' since it's a string, which is the reason for the concatenation.

If this works for you then we can address null values.
Oct 6 '11 #9
ok, that worked almost perfect. on frm_hardcopies, I have it so onload it goes to a new record. when I do my dbl click, it goes to the new record and I have to navigate back 1 record to view the one I want. Is there a way to have it prevent the 'go to new record' function when I use this dbl click event?

null might not be an issue if it just goes to new record on frm_hardcopies when it opens. not sure, though
Oct 6 '11 #10
patjones
931 Expert 512MB
on frm_hardcopies, I have it so onload it goes to a new record
Whatever it is you're doing to make it go to a new record on load, don't do it!
Oct 6 '11 #11
ok. so Ill remove that then.
Oct 6 '11 #12

Sign in to post your reply or Sign up for a free account.

Similar topics

5
by: Wouter | last post by:
Hi, I had a javscript what does the follow. When a checkbox is yes then i will see a form field. When a checkbox is no i dont see the form field. Can somehelp me for that script ? Greets...
1
by: P | last post by:
Hi, Access 2002. I am trying to use the PrintOut function to print the current record of the current open form in landscape format to the default Windows printer. Any suggestion on how to set...
9
by: William Wisnieski | last post by:
Hello Everyone, Access 2000 I have a main form with a continuous subform. On the main form I have a text field called . It gets populated based on what the user selects in a field on the...
3
by: Jim Evans | last post by:
Using code and suggestions from an earkier thread in this group, I have created the following cond for the open event of a form I am opening from the button click event of another form. ...
3
by: Rosy | last post by:
Hello all. I think my request is a simple one; however, I am unable to figure it out by myself. I need a form (frmCargoEntry) to open whenever the cargo dept is chosen on another field. So when...
0
by: sean | last post by:
HI There, I am still new and making the transition to asp.net, I have a textbox control on a page and when I submit the for if the textbox contains words with spaces it only shows the first...
14
by: keri | last post by:
Hi, Simple version of the question..... How do I use the where clause of the open form command to show an account with a matching ID to be displayed when the form is opened? Eg. I select a...
3
by: birt | last post by:
I have a form with two subforms on it. On my 2nd subform I want to open a new form base on three criteria. One is a string two are integer. It has been over six years since I have done this and I...
1
by: boyd | last post by:
I am working with an Access Form and I would like to have a field that chooses it's criteria based on an option that is chosen. I am not writing this in code so I will need to be guided on how to do...
10
by: Beatrice | last post by:
I need to open a form selecting all data from a previous form i.e: Form 1) combo box 1 named "cboYearSelect" displayed as "Year" based on qry QryYearList ( only one field "Year") ...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.