Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Register Log in
  • Congratulations Mike Lewis on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Open form and show blank fields but not using NewRecord. 1

Status
Not open for further replies.
4N6Mstr

4N6Mstr

Technical User
Oct 6, 2008
40
US
Hello all and Happy Holidays!

This is more a pet peeve of mine than a problem that does not have a workaround (or many).

The standard situation is this: main form – tab control with sub forms scenario. No creation of records / no deletion, just fields’ update available. After the user opens up the main form, he / she should choose the account to work with via a combo box. Naturally, the combo box’s after-update event starts the normal locate & show adequate record routine. All nice and dandy.

However, as you might guess, what happens is that when the main form is first opened it automatically shows the first record of the underlying data, which is not desired. The ideal would be to show nothing on the fields. One of the common tricks is to go to a new record (which obviously have empty fields), another is to hide the controls (visible = false) and show them up only after the combo box is updated, yet another is to use an unbound form.

All valid workarounds; however, I cannot invest the time to revamp the whole database to use unbound forms (my preferred solution), the hide-unhide trick is somewhat annoying, and I really, really dislike the false-new-record approach. Especially when, every time that happens, a primary key (autonum) is discarded, this generates wholes in the primary key sequence without a good explanation for it.

As I said, just a pet peeve, as those are all acceptable workarounds; however, I am still looking for that clean solution, a go-to-BOF-and-show-a-nice-set-of-empty-fields solution!

Suggestions?



Any help is always apreciated!

Thx,


4N6MSTR
______________________________________________
If you don't know where you are going
It does not matter how fast you are
You will never get there
 
Sort by date Sort by votes
Just set the forms recordsource and each control's control source after you chose from the combo. To make this easier, but the recordsource in the the controls tag property. something like


dim ctrl as access.control
me.recordsource = "qryName"
for each ctrl in me.controls
if ctrl.tag <> "" then
ctrl.controlsource = ctrl.tag
end if
next ctrl
 
Upvote 0 Downvote
should say: "put the controlsource in the tag property of the control"
Also initially all controls have nothing in the control source.
 
Upvote 0 Downvote
NajP,

That is more like it. Very clean. It is still a workaround (no offense), but very sharp. I like this one.

I will have to work a bit with the code to address the subforms, but I think this is the best I saw 'till date.

Thx!

Any help is always apreciated!

Thx,


4N6MSTR
______________________________________________
If you don't know where you are going
It does not matter how fast you are
You will never get there
 
Upvote 0 Downvote
This works with any form. Put in a standard module
Code: 
Public Sub unbindControls(frm As Access.Form)
  Dim ctrl As Access.Control
  For Each ctrl In frm.Controls
    Select Case ctrl.ControlType
      Case acListBox, acComboBox, acTextBox
        ctrl.Tag = ctrl.ControlSource
        ctrl.ControlSource = ""
        ctrl.Locked = True
      Case acSubform
        ctrl.Tag = ctrl.SourceObject
        ctrl.SourceObject = ""
    End Select
  Next ctrl
End Sub

Public Sub bindControls(frm As Access.Form)
  Dim ctrl As Access.Control
  For Each ctrl In frm.Controls
    Select Case ctrl.ControlType
      Case acListBox, acComboBox, acTextBox
        ctrl.ControlSource = ctrl.Tag
        ctrl.Enabled = True
      Case acSubform
        ctrl.SourceObject = ctrl.Tag
     End Select
  Next ctrl
End Sub

Public Sub unbindForm(frm As Access.Form)
  frm.NavigationButtons = False
  unbindControls frm
End Sub

Public Sub bindForm(frm As Access.Form)
  bindControls frm
  frm.NavigationButtons = True
End Sub
on the forms open event call

unbindForm(me)
Then when you want to bring it back (after the combo entry)
bindForm(me)

You probably need and extra if check so as not to lock your combo.
 
Upvote 0 Downvote
BTW it is either
call unbindForm(me)
or
unbindForm me

this gives and error
unbindForm(Me)
 
Upvote 0 Downvote
Just awesome, MajP. Thx!

Any help is always apreciated!

Thx,


4N6MSTR
______________________________________________
If you don't know where you are going
It does not matter how fast you are
You will never get there
 
Upvote 0 Downvote
4N6Mstr said:
Especially when, every time that happens, a primary key (autonum) is discarded, this generates wholes in the primary key sequence without a good explanation for it.
Click to expand...
I don't see why that is a problem. The one and only purpose of a Primary Key is to hold a unique value. What difference if there are "holes" in the records? After all, when it comes time to delete an existing record, you will also make a "hole".
 
Upvote 0 Downvote
Joe,

The false-new-record does not leave a trace, thus the missing primary-key non-existance is not documented. A deletd record can me made to leave a trace (who, when, why). In critical databases, that need to be audit and scrutinized for unexpected ocurrences, that can be a fundamental difference.

Besides, if you refer back to my original post, you'll see that I clearly stated that this issue was a pet peeve of mine, not one that did not have (valid) workarounds.

Just personal preference.



Any help is always apreciated!

Thx,


4N6MSTR
______________________________________________
If you don't know where you are going
It does not matter how fast you are
You will never get there
 
Upvote 0 Downvote
You may want to consider, providing some nicer interfaces. I would not have this problem because in my opinion you really should seperate data display, data navigation, and data entry.

Think of the MS Outlook paradigm. You have a display that shows summary information for emails in a continous, sortable, filterable view. You can then select from this view to read or edit an existing email. Or you have capability to enter a new record (email). That is how I would design most of my dbs. Provide a summary in continous view that is sortable, navigable, and filterable. Then provide functionality (usually in pop ups) to view/edit/add record details.

The personal preference is fine; however, your argument is invalid and some of the assumptions could get you in trouble. If you are using an autonumber and not generating your own primary key, than there is no way to ensure contigous values and you should never use this as a way to verify existence/loss of data. Using more robust databases you may have more success, but using Access this would be very unsafe and impossible in a multiuser environment. No developer of a critical database would ever use this approach as a means to verify data integrity. However, if you are rolling your own PKs then there is absolutely no reason you could not simply go to a new record.
 
Upvote 0 Downvote
MajP,

All valid points. However, this is a database that I'm just refining the user interface and doing some fine tuning on the audit function it already has. The "lost" primary keys were getting in the way. There are certainly better ways to audit, but that problem will have to wait for the next review. Then again, the false-new-record just bothers me, probably it is a very acceptable solution, that i prefer not to use.

When designing from scratch I use a similar approach to the one you recommend. I am specially found of unbound form / controls for creating, editing records and validating data before comiting the changes. Continuous forms for records use (review / search / filter/ etc.).

Thank you.

Any help is always apreciated!

Thx,


4N6MSTR
______________________________________________
If you don't know where you are going
It does not matter how fast you are
You will never get there
 
Upvote 0 Downvote
I understand. My only point was that an autonumber can and should only be counted on to provide a unique value. Nothing else, nothing more. It should not be expected to be contigous or even ordered. There are several things that can cause gaps in the sequence as well as make it go random. If it is bothering you then you are much better off rolling your own incrementing PK. You then can ensure at least it is ordered and contigous (except for deletions). It then would provide a simple solution to avoid the "lost PKs". The limitation is that you have to code forms to handle all the incrementing. However, in AC2010 this may be overcome. Although I have not seen it yet I have read that in 2010 there are data macros.
This would then do away with the drawbacks of rolling your own autonumbers.
 
Upvote 0 Downvote
Interesting. I'll check it out. Thx, again, MajP.

Any help is always apreciated!

Thx,


4N6MSTR
______________________________________________
If you don't know where you are going
It does not matter how fast you are
You will never get there
 
Upvote 0 Downvote
Hi 4N6MSTR,

To answer your original post, just place a select query as the record source for the opening form.

In this query, just select something that will always return zero records and that should solve your display issue.

Hap...

Access Developer [pc] Access based Accounting Solutions - with free source code
Access Consultants forum
 
Upvote 0 Downvote
Status
Not open for further replies.

Similar threads

TrekBiker
  • Locked
  • Question
Form Data Not Updating
Replies
6
Views
281
dhookom
dhookom
TrekBiker
  • Locked
  • Question
Forcing Form Updates
Replies
2
Views
231
TrekBiker
TrekBiker
breezett93
  • Locked
  • Question
Function that find all controls on a form and puts their tab index in numerical order
Replies
19
Views
412
dhookom
dhookom
renigar
  • Locked
  • Question
Trying to add hyperlinks to pdfs through form entry
Replies
0
Views
185
renigar
renigar
priac
  • Locked
  • Question
Create a Duplicate of an Existing Record in Microsoft Access but leave some fields blank.
Replies
7
Views
583
Andrzejek
Andrzejek

Part and Inventory Search

Sponsor

Back
Top