Main form and subform to behave exactly like a split form

[sessionone]

Hi all,



Sorry, if this has been asked before. I could not find an answer in 6 hours of googleing... I am totally new to Access.

I have a simple database. Two tables, one for main data, another for users (usernames, passwords). So the form in question relates to this one main table.

For data entry, I would like to have a split form. One form for data entry, another form as data sheet of all records. This is exactly what I get when I do a split form, but I can't use split form in Navigation form.

So, basically, I am looking for a away to enter data and see it appear in the table, all on the same form.

I thought this is going to be fairly simple, but I can't find a way.

Thank you for your help.

SessionOne

[isladogs]

Both split forms and navigation forms are notoriously difficult to modify. Combining them may well be almost impossible.

Have a look at this emulated split form which overcomes many of the issues with the 'real' version and is more adaptable.
https://www.access-programmers.co.uk...d.php?t=294421
It may be possible to use it with a navigation form though no promises as I haven't tried it

[sessionone]

Thanks. I suppose, for navigation purposes, it is best to use a tabbed form?

[Micron]

There is a thread that claims to have a solution. See johnwwallner post here

[sessionone]

Hi All,

Thank you for your suggestions.

Perhaps the solutions are too advanced for what I am looking for. And perhaps I did not give enough info about what I am trying to achieve, so sorry for this.

I have a frmMainDataEntry. In it I have 2 forms: sfrmDataEntry, which is set to data entry, and sfrmDataView, which is datasheet from the main table. The datasheet is sorted by the record number (not autonumber id), where the newest record with the highest number appears on top.

So, what I am looking for is a way to see the record added in real time. I know that it is added, but I need to re-sort the view in the datasheet form to see that it actually is there. Basicaly, I would like the user to be confident that, upon clicking a button, the record is where it is supposed to be .

Do I need to create a query for this?

Thank you again for your help.

SessionOne

[isladogs]

Have you tried requerying the datasheet after a new record is added.

[Micron]

sessionone: If this is a form / subform setup now, then I was totally off topic.

For data entry, I would like to have a split form.

In that case, you should Google split form if that's what you thought you had or wanted. If you cannot follow ridders52 suggestion, post the names of your subform AND your subform control (the control on the main form that holds the subform) and we'll try to help with the code line for requerying the subform. Even better would be those names and event code that runs after the record has been added.

[sessionone]

Micron,

Sorry, I am new to access and access VBA (I've done a bit of word vba years ago), so please please be patient As per ridders52 suggestion, and from reading through his posts, I decided not to go with split forms.

This is what I have:

1. frmDataEntryMain (main form, which is container for the other 2 forms)
2. frmDataEntryMain holds sfrmDataEntry (created from tbl2018), which is set for data entry "yes", and sfrmDataView, a data sheet also created from tbl2018.

Sorry, I do not understand what you mean by subform control.

At the moment, I am looking at how to requery and where to put the requery command. I would like to have a button on sfrmDataEntry, which would save a new record and update sfrmDataView.

Also, frmDataEntryMain will go into a new tab on Navigation form.

I hope it's more clear now.

thank you.

sessionone

[Micron]

Sorry, I do not understand what you mean by subform control.
At the moment, I am looking at how to requery and where to put the requery command. I would like to have a button on sfrmDataEntry, which would save a new record and update sfrmDataView.
Also, frmDataEntryMain will go into a new tab on Navigation form.
I hope it's more clear now.

In design view of the main form, click once on the subform (the form that contains all the controls for the subform records). A line should appear around the perimeter. Look at the property sheet at the very top - the Selection Type: should be Subform/Subreport. This is actually a control that holds the form you're using as a subform. It's name should appear in the combo box list of object names. Much of the time you'll see developers refer to the control holding the subform as "the subform control" or similar, rather than calling the control "the subform". Don't know if we're exactly 100% correct doing that, but that seems to be the norm. Thus when I say subform, I mean the form itself. You should now be able to figure out what the name of the subform control is.

When you reference a subform property, method or one of its controls, you have to get the hierarchy correct. Putting a form/subform combination on a nav form just complicates things, and many developers will not use a nav form. That's not just laziness or loathing complications. One of the main problems with a nav form is that only one form can be loaded at a time, so it's more difficult to pass data from the 1st to the 2nd. Another is that you can't use nav button code to refer to any aspect of the current form as clicking the button unloads it. Regardless, the hierarchy ceases to be a "parent/child" thing and becomes a "grandparent/parent/child" thing. You might want to consider a custom switchboard (again, I would avoid the built in switchboard form, but that's my bias) to provide structured access to forms and reports. If you feel real adventurous, create your own ribbon.

Here's my notes on subform references, including nav form should you stay that course:

See also http://access.mvps.org/access/forms/frm0031.htm

referencing CONTROLS on subform:
[Forms]![Main form name]![subform control name].[Form]![control name on subform]

e.g.
[Forms]![frmNP]![subNPfrm].[Form]![txtNPno]
frmNP is main form name
subNPfrm is name of subform control on frmNP
txtNPno is the textbox control name

referencing subform PROPERTY (e.g. recordset property such as Recordcount) using object collections syntax:
Forms("MainFormName").Controls("subformControlName ").Form.Recordset.Recordcount
e.g. Forms("frmWhoUsesVendor").Controls("subWhoUsesVend or").Form.Recordsource
NOTE: again, subformControlName IS NOT THE SUBFORM NAME

Forms("frmTempDetl").Controls("frmTimeDtlSub").For m.Dirty

NAVIGATION FORMS
[Forms]![NavigationForm]![NavigationSubform].[Form]![controlName]

FYI - If you use wizards Access will usually give the subform control the same name as the form it holds. Can be confusing when coding, so I don't. If I use a wizard to build a normal form, I do not keep the Access names as they end up being the same as the fields they're bound to - another "bad" thing, IMHO.

Lastly, if I seemed impatient I apologize. I can be somewhat direct at times and too verbose at other times. My wife would agree with the latter but not the former

[sessionone]

Thanks. I will not use the Navigation form. I really appreciate your advice.

So, this is what I have: main form selection type is Form, its name is Form as well... sfrmDataEntry and sfrmDataView are both subforms/subreports. Is this the way they're supposed to be?

I am running into different problems now and it's getting really frustrating. Having a button to update/requery records in sfrmDataView is kind of pointless as new records are being added automatically... There are lots of posts regarding preventing auto adding of records, but access throws up an error "problem occurred while access was communicating with the OLE server or ActiveX Control". This could be another topic, I don't know.

I have a feeling there is a fault in the design of my fairly basic database...

[Micron]

It sounds like the 2 subform controls are sfrmDataEntry and sfrmDataView. From that, you could try code based on the syntax samples I gave.
Not sure what you mean by the rest of it. Records might be "automatically" entered into tables that a form is "looking" at, but the view of records in that form will never change unless you do something to change it. That could be requery the open form, or close it. When it reopens, it will present the latest data. I would think that's a good enough reason to want to requery the form, but it sounds like you don't think so. Then again, I have no idea what you mean by automatically.

So not sure where you want to go from here.

BTW - Likely that the name isn't "Form" as you posted. For application objects (forms, sections, etc) what you're seeing is the object type. For objects that you added, you see the object name. If you really named your form Form, let me know. If you want to switch tracks and abandon this problem in favor of looking at db design, you could start a new thread if you think that would be appropriate. In that case, I'd mark this one as solved so others don't waste time reading through it when it no longer applies.

[sessionone]

Micron,

No, the name of the main form is not Form, it is frmDataEntryMain, but I wasn't sure, because it was not in the drop down list under the Selection type.

Ok, this is how I would like it to work. User opens data entry form, on a new blank record. User enters data, clicks a button, new record appears on the data sheet. Now, the record is saved in the table if the user clicks outside the entry form or closes the form. Keeping in mind that I know the users very well and I know that they make a lot of mistakes (this is a project at work to keep myself motivated and learn something new), I would like to restrict them from saving useless records. I need this for statistics and reports as well. Every single record must be genuine and useful. Also, each record is assigned an incremented number (not an autonumber id as it is not useful in this case). If I have incomplete records, my reports will be off.

To solve this, I tried this:
https://answers.microsoft.com/en-us/...6e60438b4?db=5

But I get an error, and I have no idea what it means:


Also, tried this on a test db, but it doesn't seem to have any effect:
http://www.databasedev.co.uk/unbound...-add-data.html

So, in this case I have two problems. I am sure that by following your guide to hierarchy I can figure out how to get the datasheet view updated by pressing the button. But I can't move forward until the saving of records is solved.

P.S. I am not totally hopeless So far, I have managed to create a separate search form from a query to search by a few field and by date range. This one so far has been working perfectly.

Thank you for your patience

[Micron]

Shouldn't be too hard to accomplish, but there are some things you may not have thought about.

Now, the record is saved in the table if the user clicks outside the entry form or closes the form

- why let unconscious actions (e.g. merely clicking off form) save a record? How does this promote entering valid records? Better to 1) force use of a button and 2) prompt to save if not consciously saved when form is closed. IMHO this would be Save/Close and Cancel/Close buttons. Could be more elaborate, but intermittent saves introduces issues.

- Opening said form as data entry means that no other records would be visible, thus cannot be edited. OK?
- If no, same form needs to be opened as data entry one time, edit another and something beforehand drives this decision. 2 identical forms for different purposes is not good design
Are these the same thing?

Every single record must be genuine and useful... If I have incomplete records, my reports will be off.

To prevent incomplete records, must require all or at least the required fields to be filled in.

To accomplish the goal as you have described it, there is no need for a subform or split form at all. Why not just create the record in a single form? There's no point in repeating it in a subform below. Those are meant to show the many (subform) related to the one (main form) as in Purchase Order (main) and PO line items below (subform). Achieving a degree of accuracy doesn't require a subform. In fact, that will only complicate things unnecessarily. Your problems seems to be one of data integrity and/or validation. As for the record id, what is it? If it's meaningful data (e.g. work order number) it's likely required. If it's just to make records unique, it's not. Last but not least, importance of ID being sequential, or whether or not it is meaningful to the user determines when ID should be created in a multi user db - at start of record, or at save.

I understand the error but there's no way I could pinpoint it without seeing the db.

[sessionone]

Better to 1) force use of a button and 2) prompt to save if not consciously saved when form is closed. IMHO this would be Save/Close and Cancel/Close buttons. Could be more elaborate, but intermittent saves introduces issues.

Yes, but in order to make the entering of data more streamlined, I would like to have "save and move to another new record" button. It would be a pain to open and close the form every time new data is entered. This is a time focused task. I tried putting the button on the form, but I get the error. I am sorry, I can't share db as it holds nearly 10K clients personal data.

- Opening said form as data entry means that no other records would be visible, thus cannot be edited. OK?
- If no, same form needs to be opened as data entry one time, edit another and something beforehand drives this decision. 2 identical forms for different purposes is not good design
Are these the same thing?

No, I have another form, based on a query, to edit records.

To accomplish the goal as you have described it, there is no need for a subform or split form at all. Why not just create the record in a single form? There's no point in repeating it in a subform below.

Ok, so how do I let the user know that the record has been saved. A message box saying that it was added is not good enough, I think. For peace of mind for the user, I would like them to know that the record is indeed where it should belong. That's why I was thinking of adding another form below the data entry form, so the user sees that the row has appeared on top of the most recent record.

As for the record id, what is it? If it's meaningful data (e.g. work order number) it's likely required. If it's just to make records unique, it's not. Last but not least, importance of ID being sequential, or whether or not it is meaningful to the user determines when ID should be created in a multi user db - at start of record, or at save.

The record id is a client application number. It is meaningful, as it shows how many applications have been accepted. So, it must be sequential. And yes, I have looked into the problem of clashing record numbers. I am not sure what is the best practice/logic to assign the unique number. Probably after pressing the Save button? The data will be entered by 3-4 employees at the same time.

[sessionone]

OK, so this is a ... nightmare. I've spent hours looking for a solution. Tried countless suggestions in other forums, and I still can't get the thing to work as I would like.

I have created a new test db. Could you please take a look at what I am doing wrong? Why am I not able to see the newly added records on the data entry form?