ListBox x Subform DoubleClick()

[Garcimat]

Hi guys
I have a list box on my form with data from a query, in the double click event it would collect the data from the record clicked, send it to another query and then open a form with that specific record. I want to change the format (as an visual alarm) when that record expire, not possible with ListBox so I changed it to a Subform, (Continuous SubForm, fields Unable=yes and Locked=yes)
I have to achieve the same as before, when double clicked it has to open a form(frm_PTF2) with that specific selected record... I have been trying to fix this for the whole day and I am almost going back to the previous design. Below is my code for the List box, thank you in advance.


Private Sub lst_ActivePermits_DblClick(Cancel As Integer)
'On Error GoTo ErrorBin_Err

'gvar_SWONumber and gvar_SWONumber Global Variables

gvar_SWONumber = lst_ActivePermits.Column(0) 'capture value 1 from list box (SWO Number)
gvar_SWONumber = lst_ActivePermits.Column(1) 'capture value 2 from list box (PTF Number)

DoCmd.Close acForm, Me.Name
DoCmd.OpenQuery "qrs_ptfnavig", acViewPreview
DoCmd.OpenForm "frm_PTF2", acNormal


'ErrorBin_Exit:
' Exit Sub
'ErrorBin_Err:
' MsgBox Err.Number & " " & Err.Description
' Resume ErrorBin_Exit

End Sub

 

[June7]

Fix what? Exactly what is the issue - error message, wrong result, nothing happens?

If the issue is with a subform, why do you post code for a listbox?

Why do you open query?

 

[Auntiejack56]

Hiya,
I wouldn't use a continuous form. Use a datasheet, and set the extreme left column to a hyperlinked record id which, when clicked, opens the record for editing.
For an example of how this looks, go to www.bluebridge.com.au and play the video - at 1:49 you'll see that conditional formatting on a datasheet can show your distinctive records, and the hyperlink can be used to edit the record.
In general, as you have described for your continuous form, I ensure that the datasheet itself doesn't allow editing.
Jack

 

[Auntiejack56]

Postscript: datasheets can look a bit amateurish because the data is usually bunched up to the left, leaving empty space.
To fix this, you can automagically expand the columns to fit - go to the bluebridge website , click the knowledge base menu and select Autosizing Datasheets. The code expands the visible columns proportionally to fill the space. In most cases, Access remembers when you resize a column, and reopening the form will ensure it is proportionally adjusted.

 

[Auntiejack56]

Post postscript: if you decide to go with using Conditional Formatting on a datasheet, you will find a fun thing - that is, that the same conditional format formula has to be applied to every column individually to make the whole row show the highlight colour. However, if you enter the formula on one column, you can then copy and paste the format to all the other columns in one go. So keep that in mind and you'll make your life ([columncount] -1) times easier.
Jack

 

[Garcimat]

Fix what? Exactly what is the issue - error message, wrong result, nothing happens?

If the issue is with a subform, why do you post code for a listbox?

Why do you open query?

If the issue is with a subform, why do you post code for a listbox?

That is the code for my List box, I am trying to double click on the subform and open another form with that current record in the same way it was doing with the list box.

Why do you open query?
That is the way I found to get this working, I know that there is a way to avoid all the queries, but I do not know how to do that... I am doing a sql training and clean all these queries is in the To Do list.

 

[Garcimat]

Hiya,
I wouldn't use a continuous form. Use a datasheet, and set the extreme left column to a hyperlinked record id which, when clicked, opens the record for editing.
For an example of how this looks, go to www.bluebridge.com.au and play the video - at 1:49 you'll see that conditional formatting on a datasheet can show your distinctive records, and the hyperlink can be used to edit the record.
In general, as you have described for your continuous form, I ensure that the datasheet itself doesn't allow editing.
Jack

Thanks, very interesting... I try it in a test database, I really need the conditional formatting and the ability to call an specific record on a editing form.

 

[MajP]

I am trying to double click on the subform and open another form with that current record

Normally this is done using the "wherecondition" of the docmd.openform method.

DoCmd.OpenForm method (Access)

Office VBA reference topic

docs.microsoft.com

 

[MajP]

I am trying to double click on the subform and open another form with that current record

You cannot really double click the subform, but you can put a single event handler on every control in the subform. See the discussion on building a function to serve as the single event handler.

What You Need to Know About Access Events

There is often a lot of confusion about Access Events and often answering questions posed here is difficult because of inaccurate terminology. People often say "Event" when it is unclear if they mean the actual event that takes place, the event procedure, or the event property. This thread...

www.access-programmers.co.uk

 

[Garcimat]

It is not working ..... Why do I get this msg ?

 

[Garcimat]

You cannot really double click the subform, but you can put a single event handler on every control in the subform. See the discussion on building a function to serve as the single event handler.

What You Need to Know About Access Events

There is often a lot of confusion about Access Events and often answering questions posed here is difficult because of inaccurate terminology. People often say "Event" when it is unclear if they mean the actual event that takes place, the event procedure, or the event property. This thread...

www.access-programmers.co.uk

I thought about that yesterday, but I don't know how to get the index from that record and open the editing form.

 

[Auntiejack56]

I see you have Option Explicit, which is great, but you have to explicitly declare your variable, as follows:
Dim gvar_Index
Then use that in your filter:
"txtIndex = " & gvar_Index
txtIndex has to be in the query that the form FRM_test is bound to, otherwise boom.
(You can lose the squared brackets if there are no spaces or reserved words in your variable name.)

 

[Garcimat]

Everything looks fine BUT.... doesn't work hahaha

gvar_index is a global variable on a module do I have to declare again ?

frm_ActivePTFs2

Private Sub txtRecordId_DblClick(Cancel As Integer)
MsgBox "test" ' it is working !
gvar_index = Me.txtRecordID

MsgBox gvar_index ' it is working !

DoCmd.OpenForm "FRM_Test", , , "txtIndex = " & gvar_index
'msg "Enter Parameter Value ?
'txtIndex

End Sub


this is why I call a query and then the form from the query.... 1000 queries here hahha

 

[Garcimat]

You cannot really double click the subform, but you can put a single event handler on every control in the subform. See the discussion on building a function to serve as the single event handler.

What You Need to Know About Access Events

There is often a lot of confusion about Access Events and often answering questions posed here is difficult because of inaccurate terminology. People often say "Event" when it is unclear if they mean the actual event that takes place, the event procedure, or the event property. This thread...

www.access-programmers.co.uk

Thanks, I will print it.... make understanding easy when everyone is speaking the same "language"

 

[Garcimat]

Now it is a matter of honour, I will get this to work !

 

[June7]

I have a subform in datasheet view and form double click event code to open another form filtered to record. I have a label above the subform that instructs user "Double click in left margin of a record to view lab data."

DoCmd.OpenForm "LabData", , , "LabNum='" & Me.tbxLabNum & "'"

I don't use dynamic parameterized queries.

 

[Auntiejack56]

Ah what a nightmare you are having, and of course in the end it will be something simple. The error you are seeing is often thrown by a query, when the field you are referring to does not exist in the query. The query will be the one your form is based on. Open the query by itself, and check that it opens cleanly. My guess is you'll get the same error.