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?
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: - 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
What code do you have so far?
Pat
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. - Private Sub Path_DblClick(Cancel As Integer)
-
-
On Error GoTo Err_Path_Click
-
-
Dim stDocName As String
-
Dim stLinkCriteria As String
-
-
stDocName = "frm_hardcopies"
-
DoCmd.OpenForm stDocName, , , stLinkCriteria
-
-
Exit_Path_Click:
-
Exit Sub
-
-
Err_Path_Click:
-
MsgBox Err.Description
-
Resume Exit_Path_Click
-
-
End Sub
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
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: -
Dim stLinkCriteria As String
-
-
stLinkCriteria = Me.Path.Value 'Assuming that the value you want is in the Path field
-
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: -
If (IsNull(stLinkCriteria)) Or (stLinkCriteria = "") Then
-
MsgBox "A value must be entered to launch the new form"
-
Cancel = True
-
End If
-
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
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 - Private Sub Path_DblClick(Cancel As Integer)
-
-
On Error GoTo Err_Path_Click
-
-
Dim stDocName As String
-
Dim stLinkCriteria As String
-
stLinkCriteria = Me.Path.Value 'Assuming that the value you want is in the Path field
-
-
-
stDocName = "frm_hardcopies"
-
DoCmd.OpenForm stDocName, , , stLinkCriteria
-
-
Exit_Path_Click:
-
Exit Sub
-
-
Err_Path_Click:
-
MsgBox Err.Description
-
Resume Exit_Path_Click
-
-
End Sub
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.
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
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: - 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.
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
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!
ok. so Ill remove that then.
Sign in to post your reply or Sign up for a free account.
Similar topics
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...
|
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...
|
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...
|
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.
...
|
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...
|
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...
|
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...
|
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...
|
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...
|
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")
...
|
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...
|
by: ryjfgjl |
last post by:
ExcelToDatabase: batch import excel into database automatically...
|
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...
|
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...
|
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)...
|
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...
|
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
|
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...
|
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...
| |