Loading form fields by clicking a list included in the form.

[edson]

Hey guys, need your kind help on this one please. I saw similar threads in the forum which did not solve my problem.


My form FrmAtzPDC has a list named LstPDC.
I want to click in the list and populate frm fields in order to be able to Change, Delete, etc.
I have declared “Option Explicit” on the top of the code. I set LstPDC propertie for new entries as No.
The list source query includes every single field from the Table updated by FrmAtzPDC however when I built LstPDC I hided some fields that are irrelevant to show in FrmAtzPDC.

This is the code I am using with no success:

Private Sub Form_Load()
Me.Caption = "Atualização do Plano de Contas"
‘ Enabling buttons
Me.bt_novo.Enabled = True
Me.bt_alterar.Enabled = False
Me.Bt_salvar.Enabled = False
Me.bt_excluir.Enabled = False
End Sub
 
Private Sub Form_Open(Cancel As Integer)
‘ cleaning up eventual bad record
Dim SQL As String
DoCmd.SetWarnings False
SQL = "DELETE * FROM TblPlanoDeContas WHERE CTACLASSE is null"
SQL = "DELETE * FROM TblPlanoDeContas WHERE CTANUM is null"
SQL = "DELETE * FROM TblPlanoDeContas WHERE CTATIPOFV is null"
SQL = "DELETE * FROM TblPlanoDeContas WHERE CTATIPOCE is null"
SQL = "DELETE * FROM TblPlanoDeContas WHERE CTAGRUPO is null"
SQL = "DELETE * FROM TblPlanoDeContas WHERE CTANOME is null"
SQL = "DELETE * FROM TblPlanoDeContas WHERE CTADESCR is null"
DoCmd.RunSQL SQL
DoCmd.SetWarnings = True
Me.Requery
Me.LstPDC.Requery
End Sub
 
Private Sub LstPDC_Click()
‘ Trying to load form fields based on the list
Me.Caption = "Atualização do Plano de Contas"
On Error Resume Next
Dim intrec As Integer
intrec = Me!LstPDC.Column(0)                          ‘variable is the list primary key
Forms![FrmAtzPDC].SetFocus                           ‘to set focus on the form I am already in
Forms![FrmAtzPDC].[CTAID].Enabled = True     ‘to ensure form primary key is enabled
DoCmd.GoToControl "CTAID"                           'to set focus on form primary key
DoCmd.FindRecord intrec                                 'to find the desired record according to the variable value
End Sub

What am I missing?

[June7]

Why are you using code to populate fields? Are you also using code to save the edited data?

Are you not using bound form?

[edson]

Hi Jun7. How are you? I'm glad you are the one standing by me. It makes me comfortable.
I have a list in the form. I click in the list to select a particular record of the form source table and populate the form fields. From there I use the form to change or delete the record. List is useful for the user as he can see what is already registered in the table.
Yes, I do have a bt Save in the form which has a code to save new records and record changes. Codes include some "pokayokes" to avoid mistakes. Also have a module Audit Trail to monitor user made changes.
I do not understand what bound form stands for.
I hope I unswered your questions correctly.
Hug

[June7]

A bound form has a RecordSource that is a table, query, or SQL statement. Data entered/edited in controls passes directly to the associated table. No code required.

[edson]

I see what you mean. I'll read and learn about this interesting resource. However it seems to me that my need goes to the opposite way, meaning: The user must be able to see what is already in the table before proceed his changes or deletions. Let me give you a couple of examples: Imagine a user inputing daily expenses in a form. He does not see the table right? If he gets interrupted, or even in the next morning, a list included in the form will help him to see, for instance what was the last input made and continue from there. Or, if he found a wrong value, he needs to know which record he has to change. List will help him to find the wrong record.
That is why I want to click in the list to select a record and show it to the user. Does it make sense?

[June7]

Form can display existing records for viewing/editing as well as allow entry of new records. Options:

1. apply filter to the same form based on selection in an UNBOUND combobox or listbox

2. open another form filtered to desired record based on selection in an UNBOUND combobox or listbox

Either way, edit record, data passes directly to table. The filter or open form action would require some code, but much simpler.

[edson]

June7, option 1 was exactly what I did: I applied a filter to the same form based on selection in a UNBOUND listbox named LstPDC. The part of the code I need help to make work is the last part of it. Please, see below. F8 tells me that the variable intrec assumes the correct value but it jumps or by-pass or does not read the line "DoCmd.FindRecord intrec", or in other words, does not find the record. That is what I need to fix.

My original intent to give you the entire scenario to save your time ended up complicating things... I shoud have been more objective/specific in my original question. I apologize for that. Sometimes language barrier can be a pain in the neck.

Private Sub LstPDC_Click()
Me.Caption = "Atualização do Plano de Contas"
On Error Resume Next
Dim intrec As Integer
intrec = Me!LstPDC.Column(0)                          ‘variable is the listbox primary key
Forms![FrmAtzPDC].SetFocus                           ‘to set focus on the current form
Forms![FrmAtzPDC].[CTAID].Enabled = True     ‘to ensure form primary key is enabled
DoCmd.GoToControl "CTAID"                           'to set focus on form primary key
DoCmd.FindRecord intrec                                 'to find the desired record according to the variable value
End Sub

[June7]

I never used FindRecord. I have used:

1. set form filter property, review: http://www.allenbrowne.com/ser-62.html

2. Bookmarks, example:
Me.RecordsetClone.FindFirst "Submit.LabNum='" & Me.OpenArgs & "'"
Me.Bookmark = Me.RecordsetClone.Bookmark

[edson]

Hello June7,

Even knowing you don't use FindRecord command I had to attach my example since I did my best and still did not manage to make any of your previous suggestions to work. I need some solution for that.

I would appreciate if you'd take a look to find if there is a way around. Fixing coding lines would be very welcome if you will.

I need to click in the unbound listbox included in the input form FrmLancDesp and be able to fulfill the form with selected record from listbox, so from that point on I can either delete or change the record thru the form buttons coding.

Unbound Listbox is a must since allows me to actually see what I am doing (which record I am selecting).

Thank you very very much for your time and patience.
Have a good day.

Edson

[rpeare]

you have the DATA ENTRY property of your form set to YES which is preventing the requery from working correctly.

So here's what you do

1. Change the DATA ENTRY property to NO
2. Change the SQL driving the form to:

SELECT TblLancDespesas.LDID, TblLancDespesas.LDDTREG, TblLancDespesas.LDHRREG, TblLancDespesas.LDCTANUM, TblLancDespesas.LDDTDESP, TblLancDespesas.LDVALOR, TblLancDespesas.LDFPGTO, TblLancDespesas.LDMOT, TblLancDespesas.LDFORMAOCULTA, TblLancDespesas.LDCTANUMOCULTA FROM TblLancDespesas WHERE (((TblLancDespesas.LDID)=[forms]![frmlancdesp]![Lstdespesa]));

3. uncomment the me.requery command in the ON CLICK event of the listbox Lstdespesa

Your form will be populated with the appropriate record when you click your list.

[edson]

you have the DATA ENTRY property of your form set to YES which is preventing the requery from working correctly.

So here's what you do

1. Change the DATA ENTRY property to NO
2. Change the SQL driving the form to:

SELECT TblLancDespesas.LDID, TblLancDespesas.LDDTREG, TblLancDespesas.LDHRREG, TblLancDespesas.LDCTANUM, TblLancDespesas.LDDTDESP, TblLancDespesas.LDVALOR, TblLancDespesas.LDFPGTO, TblLancDespesas.LDMOT, TblLancDespesas.LDFORMAOCULTA, TblLancDespesas.LDCTANUMOCULTA FROM TblLancDespesas WHERE (((TblLancDespesas.LDID)=[forms]![frmlancdesp]![Lstdespesa]));

3. uncomment the me.requery command in the ON CLICK event of the listbox Lstdespesa

Your form will be populated with the appropriate record when you click your list.

Wonderful !!! It is working fine now.
How come this can be so simple for you?... Well, I hope I'll get to your level someday. Thank you very very much.

P.S.: Well... Access is funny and can be logically mean sometimes... How could I imagine that an entry form should be set NO for data entries? eh, eh, eh...

[June7]

Setting DataEntry to Yes means allow ONLY new record entry so setting to No means allow ANY edit/data entry (existing and new records). Yes, it's not obvious when first encountered. I remember puzzling over that property when I started learning Access.

[rpeare]

I didn't know it was the data entry property, I've never used that property because I don't use bound forms. I just knew from the look of it it should work and went looking for the reason why. I built a new form with the click mechanic working as I wanted then went looking at the form properties between the two until I found a likely candidate.

You see, in asking a question you have taught me something!

[edson]

Setting DataEntry to Yes means allow ONLY new record entry so setting to No means allow ANY edit/data entry (existing and new records). Yes, it's not obvious when first encountered. I remember puzzling over that property when I started learning Access.

Hello you all.

Oh, oh... Something else is puzzling my brain now.

To use the data base, the user is supposed to click twice in the data base icon to open it. Then he will login thru the appropriate form, and this main menu will pop up showing several icons to drive his navigation thru the database.
One of the icons included in the main menu is designed to open the Expenses report (the one you just helped me to fix).

Now, what is puzzling:
Everything (menu, icon, forms, properties, etc.) works perfectly when I run my database from "inside" as a developer, meaning, when I open the database thru Shift Enter Keys.
However, if I open the database as the User as described above and click the open form main menu icon I get the error message 2105 (You can't go to the specified record). Interruption highlights at the command line Me.cboLDCTANUM.SetFocus at the form load event.

How come it Works as a Developer and does not work as a User? Any ideas, please? Did I provide enough info or do you need me to send the database?

Thanks

[June7]

Do you really need to set focus on the combobox with code? Have you looked at the TabOrder and TabStop properties?