Continuous Form New Record at Top

[Maria]

Hello,

I have created two sub forms one being a data entry single form and the second being my coninuous form. When I enter the data into the single form I can see it in my continuous form but when I want to enter more data for that same person, it will delete the previous entry in the continuous form. I know I am suppose to go to the Properties and then Click on Events to write an Event Procedure. Which event procedure should I use and what should I write?

[HansV]

Welcome to Eileen's Lounge!

Access will always display the blank new record at the bottom, whichever way you sort the records.

If you want to enter a new record, you can click the New record button on the toolbar (Access 2003 or before) or the New button in the Records group of the Home tab of the ribbon (Access 2007 or later), or the New record button in the navigation buttons of the form (all versions).

You could place a command button on the form that takes you to a new blank record. If you turn on Control Wizards (the "magic wand" button on the Toolbox in Access 97-2003 or the Use Control Wizards button in the Controls group of the Design tab of the ribbon in Access 2007 or later), you can make Access create the necessary code automatically:
- Place a command button on the form.

x440.png

- Select Record Operations in the Categories list, then Add New Record in the Actions list.
- Click Next and specify whether you want a text caption or a picture on the button.
- Click Next and specify a meaningful name, e.g. cmdNew.
- Click Finish.

[Maria]

The first one is the single entry form and the second is the continuous form (where the data goes when I enter it in the single entry form). Whenever I add a date in the TrasDate field and I press enter it adds it in the continuous form at the bottom. However, if I want to add another date in the "Single Entry Form" it adds it but the "Continuous Form" removes the previous information. All of these forms and sub forms are linked with a unique ID. Is there a way that the "Single Entry Form" will be blank after I enter the date and press enter without doing a command button for a new record? Thanks, Maria

[Wendell]

Hans seems to be away at the moment, so I will step in. You are trying to make Access do something that the original designers of it chose not to implement, so it gets rather complicated. In theory, if your data entry subform is set to data entry only, is in single record view, and has no navigation controls, then you should be able to do what you want. It requires both forms to be linked to a data element on your main form, and will require some VBA code on the After Insert event of the data entry form to force an immediate refresh of the continuous form. Also you want to make sure that the enter key behavior in the data entry subform is set to move to the next record.

It would be easier to assist you if we knew a couple of additional things: a brief description your experience with Access, and what version of Access you are working with (Access 2010 adds some additional ways of doing things)

[HansV]

You could do the following:

Open the single form in design view.
Select the TrasDate control.
Activate the Properties window / Property sheet.
Activate the Event tab of the Properties window / Property sheet.
Click in the On Exit event.
Select [Event Procedure] from the dropdown menu - this will take you to the form module in the Visual Basic Editor.
Make the code look like this:

Code: Select all

Private Sub TrasDate_Exit(Cancel As Integer)
    On Error Resume Next
    RunCommand acCmdRecordsGoToNew
End Sub

(The name TrasDate might be different)
Switch back to Access.
Close and save the form.
Now test what happens when you tab out of the TrasDate control.

[Maria]

Thank you for the information. The Access I have is 97. I tried the what you suggested and this is the error that comes: The expression on Exit you entered as the event property setting produced the folloing error: A problem occurred while Microsoft Access was communicating with the OLE server or Active XControl. *The expression may not result in the name of a macro, the name of a user-difined function or [Event Proceudre].*These may have been an error evaluating the fucntion, event, or macro.
When I click ok and close the window and return again to the widow it appears in the continious form like it's suppose to be. How can I resolve the error?
Thank you,

Maria

[HansV]

You could try the following:
- Create a blank new database.
- Import all database objects from the problem database into the new one.
- See if the problem occurs in the new database too.

If it does, would it be possible to create a stripped down copy of the database, then compact it (Tools | Database Utilities | Compact and Repair Database), zip it and attach it to a reply? Make sure to remove sensitive or proprietary information from the copy.

[Maria]

I will send it as new post to attach the file. The error doesn't occur but I still can't get the single entry from to be blank after I enter the information.

[Maria]

I am unable to attach the file.

[HansV]

Did you zip the database? Was the zip file too big? If so, you can e-mail it to the admins (the same address you already sent a mail to); I'll get it there.

[HansV]

Hello Maria,

I have received your database by e-mail. (You could have attached it to a reply here, the zip file is only 188 KB.)

The main form and subform are linked by the StuNum field, but of the 43,500 records 42,814 have a blank StuNum. These 42,814 records will not be displayed in the subform.
Of the remaining 686 records, most have a unique StuNum except for 123 which occurs thrice.
For the unique StuNums, the subform will only display a single record - that of the current StuNum in the main form.

The main form doesn't have navigation buttons, so it is difficult for the users to know which record they are editing.

Could you explain what the significance of StuNum is? Should it be unique?
And what is the purpose of the main form/subform setup?

[Maria]

Hi,

Actually that is part of the whole database. The whole database displays the navigation buttons. For instance the navigation button has the information where I pull the information with the StuNum(Student Number) and when I pull the information I enter the bus information and the date the ride occurred. This is where I get stuck. I had it when it was continuous form and as you can see I can enter may dates with bus information. Basically, I have to enter the every time a student rides a bus on a give day. If I have the continuous form, I would have to scroll down to put the dates on. So I was trying to have a single entry form and enter the information and automatically it will transfer it to the continuous form and I would not have to scroll down in the continuous form. I deleted the sensitive information such as the StuNum. The StuNum you see there are not real. That is why there are some that are blank. I just wanted to give you an example of what I was trying to do. I hope this will help with what I am trying to do. I spoke with another person where I work and she told me that I had to write a code builder in the after update to manage it. That is where I am stuck. I am not knowledgeable about code builder. Most of the time I can get it but this one is a hard one.

[HansV]

In the attached version, I have done four things (and removed the records with a blank StuNum):

1) Added an After Update event procedure for the TransDate text box:

Code: Select all

Private Sub TransDate_AfterUpdate()
    RunCommand acCmdSaveRecord
    Me.frmMonths.Requery
End Sub

This code saves the main form record when the TransDate has been filled, then requeries the subform so that the new date is immediately displayed in the subform.

2) Added an On Exit event procedure for the Comments text box:

Code: Select all

Private Sub Comments_Exit(Cancel As Integer)
    RunCommand acCmdRecordsGoToNew
End Sub

When the user tabs out of Comments, the form moves to a blank new record. This is optional, if you don't like it, simply remove the code.

3) Added an On Load event procedure for the form:

Code: Select all

Private Sub Form_Load()
    RunCommand acCmdRecordsGoToNew
End Sub

This ensures that when you open the form, it is at a new record so that you can start entering new data immediately. As soon as you enter a StuNum, you'll see the existing records for that StuNum (if any exist) in the subform.

4) Made the subform taller so that you can see more than one record at once.

db3.zip

[Maria]

Hi,

I did what you wrote and it still giving me an error. I am emaling you the new version so you see the whole thing.

[HansV]

Hello Maria,

In the future, please compact the database before e-mailing it - the database in the zip file was 84 MB (megabytes) in size. After compacting, 440 KB (kilobytes) remained - a reduction by a factor of 193! Zipped, it is only 47 KB, which is perfectly OK for attaching to a reply here.

The Form_Load event procedure of frmMonths contains a mistake: you used acCmdRecordsGoToNext instead of acCmdRecordsGoToNew.

And this time, frmMonth and frmMonths are independent of each other - they are both subforms of frmStuForm (and they appear to have switched roles - this time, frmMonths is the single form and frmMonth the continuous form.

To requery frmMonth, you need slightly different code since it is not a subform of the form running the code this time.

Code: Select all

Private Sub TransDate_AfterUpdate()
    RunCommand acCmdSaveRecord
    Me.Parent!frmMonth.Requery
End Sub