would like blank data entry record to appear at the top of continuous subform

[nnej]

Hi there! New to posting but have been helped immensely by other posts/responses. You guys rock.

Anyway - I have two tables, a "person" table w/some basic demographic information (person_id is primary key), and a "contact" table (contact_id is primary key, table includes person_id field and tables are linked one-to-many in relationships). I have created a form w/some of the "person" data displaying, and a subform w/all the related "contacts" for that person displaying continuously. All that is working just fine.

However, I'm trying to have the blank record which normally appears at the bottom of the continuous form, appear at the top of the form instead. This will prevent the need for users to scroll to the bottom of a potentially long list of "contacts" in the subform to enter a new record. Any ideas? Or is there a better way to do this that's pretty user- (and developer-) friendly?

Thanks in advance!

 

[MSAccessRookie]

I can think of two ideas.

If you build a sub-form that has a columnar layout, the last record in the display will have the * and allow the user to enter data.

If you want to keep the DataSheet, you can train the users to look for and select the "Last Record Icon" (Looks like a triangle pointing a vertical bar that is located to the right of the triangle) at the bottom of the DataSheet. This will move to the last record without scrolling.

I like the sub-form method since is has less variables to account for, and it gives me more control over the situation.

 

[Tiro Cupidus]

I'm not sure of a simple way to move new record entry to the top, but you could use the following line of code to prevent the scrolling:

DoCmd.GoToRecord acDataForm, Me.Name, acNewRec

 

[MSAccessRookie]

I'm not sure of a simple way to move new record entry to the top, but you could use the following line of code to prevent the scrolling:

DoCmd.GoToRecord acDataForm, Me.Name, acNewRec

I never thought of using the Event manager to control the DataSheet. Which event(s) would you add this code behind?

 

[Tiro Cupidus]

I never thought of using the Event manager to control the DataSheet. Which event(s) would you add this code behind?

Whichever event precedes the need to enter a new record. I suspect the form's OnOpen event.

 

[MSAccessRookie]

Well, there were a few ot0her events that I wondered about, and I am not positive that I know everything about them:

On Current
On Open (You said this one)
On Load
On Activate
On Got Focus

(There may be others too, but these are the ones that come to me now)

 

[Tiro Cupidus]

It's possible nnej might need one of those, but without knowing more about the project, it's difficult to say. It depends on what triggers the need to enter a new record in the subform.

 

[georgedwilkinson]

I've been following this thread because I wanted an answer also.

I'm going to be trying this when I get time, but I'd like to put it out there so we can kick the idea around.

I will be creating two identical sub-forms except the first will have a where clause like this: "where 1 = 0". I will make that sub-form small (vertically) and it's only purpose is to enter new records. The other sub-form will be directly below it and will have the "Allow Additions" property set to "No". The theory is that when you add a new record in the top subform, it will update the bottom subform with the changes. Then you can refresh the top subform so it goes back to blank.

Though I think this is a bit clunky (Access should do this out of the box), it could potentially solve this problem. What do you think?

 

[Tiro Cupidus]

I've done something very similar to that. I used text/combo boxes on the main form instead of an additional subform.

 

[georgedwilkinson]

Do you have a sample we could look at?

 

[Tiro Cupidus]

Here's the code behind the three buttons that operate it (New/Save/Cancel):

Private Sub btn_AddDecision_Click()
   Me.cmb_AddDecision = 0
   Me.txt_AddDecisionDate = ""
   Me.txt_AddDirID = ""
   
   Me.cmb_AddDecision.Visible = True
   Me.txt_AddDecisionDate.Visible = True
   Me.txt_AddDirID.Visible = True
   Me.cmb_AddDecision.SetFocus
   Me.btn_SaveDecision.Visible = True
   Me.btn_CancelAddDecision.Visible = True
   ArrangeScreen
End Sub

Private Sub btn_SaveDecision_Click()
   On Error GoTo Oops
   
   If Me.cmb_AddDecision = 0 Or Me.txt_AddDecisionDate = "" Or Me.txt_AddDirID = "" Then
      MsgBox "You must enter data in all three fields to save the decision."
      Exit Sub
   End If
   
   If Not IsDate(Me.txt_AddDecisionDate) Then
      MsgBox "You must enter a valid date."
      Exit Sub
   End If
   
   DoCmd.Hourglass True
   Dim sql As String
   sql = "INSERT INTO tbl_TechnicalDecisions (MCAI_ID, DecisionType_ID, DecisionDate, DirectorateIdentifier) " & _
      "VALUES (" & Me("MCAI_ID") & "," & Me.cmb_AddDecision & ",#" & Me.txt_AddDecisionDate & "#,'" & _
      Me.txt_AddDirID & "')"
   CurrentDb.Execute sql
   Me.sub_TechnicalDecisions.Form.LoadDecisions
   btn_CancelAddDecision_Click
   
Bye:
   DoCmd.Hourglass False
   Exit Sub
   
Oops:
   MsgBox Err.Number & ": " & Err.Description
   Resume Bye
End Sub

Private Sub btn_CancelAddDecision_Click()
   Me.cmb_AddDecision.Visible = False
   Me.txt_AddDecisionDate.Visible = False
   Me.txt_AddDirID.Visible = False
   Me.btn_AddDecision.SetFocus
   Me.btn_SaveDecision.Visible = False
   Me.btn_CancelAddDecision.Visible = False
   ArrangeScreen
End Sub

ArrangeScreen is a procedure to fit the subform to its contents and align the buttons correctly.

 

[georgedwilkinson]

So, you put unbound controls on your main form and update their (un) associated table via code. Correct?

But, gee whiz, I'm so lazy and my ADD is getting worse with each passing month. I was hoping there was some way to force Access to do it using native Access stuff. I guess I gotta pew pew more and QQ less.

 

[Tiro Cupidus]

I imagine a second subform would work in a similar fashion. It just didn't occur to me to use one if all I needed was a single row for new entry.

 

[nnej]

Hmmm... I will try the second subform idea. The first subform with all the records will get long with time so I don't want to turn off scrolling or make users go to the last record to enter a new one... I'll let you know if that works. Thanks for the suggestion.

 

[Tiro Cupidus]

My suggested option goes to a new record automatically -- no need for scrolling or the user to do it manually. If those were you only two concerns, it should work for you as the simplest solution.

 

[nnej]

This two-subform idea worked great and I added the other tip about setting the "allow additions" property to "no" in the second subform. It is a little clunky but it will work for my users just fine. THANKS!

One follow-up question, though... When I open the form, it now opens displaying the second subform instead of the top of the main form. Any tips on getting the form to open at the very top of the page, versus in the middle where the second subform starts?

 

[georgedwilkinson]

Glad you got it working.

Access is a little difficult in where it tries to put objects. There is no WYSIWYG design tool. When you're working with sub-forms, some times you have them dragged into the wrong area and don't realize it. Try scrolling on the outer set of scroll bars and make sure you don't have objects placed too low on the form. When I've had just one subform go rogue and get down too low, it has "displaced" my main form at the top.

Try reducing the size of your sub-form(s) and dragging the height of the main form up some.