How to make a newly-added record show up in an existing form?

[sueb]

In my IURs database (ADezii, you probably still have a good back end for this), I don't want my users to be able to change the Chart Number field, but I do want them to be able to add new ones. (In a later incarnation, I want to add a module to allow a controlled modification, but currently I have that field Locked because users have inadvertently changed it instead of searching on it.)

I have a form that allows the addition of just a chart number, and I want the currently-open form (based on chart numbers) to reflect the addition without requiring the user to exit the form and re-enter it.

I have a Requery statement where I think it should go, but it doesn't seem to be doing what I want.

Here's the code OnClick for the "Add New Chart #" Control button on the main form ("Patient/IUR Overview"):

Private Sub Add_New_Chart_Click()
On Error GoTo Err_Add_New_Chart_Click
 
    Dim stFormName As String
 
    stFormName = "Patients_Add_New"
    DoCmd.OpenForm stFormName, acNormal, , , acFormAdd
 
Exit_Add_New_Chart_Click:
    Exit Sub
 
Err_Add_New_Chart_Click:
    MsgBox Err.Description
    Resume Exit_Add_New_Chart_Click
 
End Sub
 

This brings up a tiny window with just the Chart Number as a field. (The user must exit this field with the TAB key, but I wish it would allow the RETURN key to behave in the same way. Any tips there would be appreciated, too.)

Once the user has successfully entered a new chart number, the "add" window closes itself. However, the new record is not available in the main window unless the user exits the window and re-opens it.

Any hints?

[ADezii]

Try

Forms!Patient/IUR Overview.Requery

in the Close() Event of the Chart# Form.

[NeoPa]

I would use either of :

Forms![Patient/IUR Overview].Requery
Forms("Patient/IUR Overview").Requery

The embedded space will otherwise confuse the compiler/interpreter.

As for using the enter key, I would suggest another control on your form (could be as simple as an OK button), that could be invisible if you prefer, which has its .Default property set to True. This will ensure the focus leaves the main control and therefore the changed value will be applied.

[ADezii]

@NeoPa: Thanks for catching the Space, never caught it! (LOL).

[sueb]

That's really interesting, NeoPa! So if I simply place a non-Visible button on my form, and set its .Default property to True, then it's the thing that will catch the return key?

I'm going to try that and post back how it works--thanks!

[sueb]

So it turns out I still had on the "add" form a left-over, unused, invisible button that no longer has any code attached to it. I set its .Default property to Yes. Pressing enter after typing a new chart number in the form's field seemed to do nothing except clear the field; it did not close the form (although it turns out that it did add the new record), and when I clicked the window's standard exit button to close it, I got an error: "Run-time error '2501': The close action was canceled." The debugger stops on line 6 of the following code:

Private Sub ChartNum_AfterUpdate()
    stDocName = "Patients_Add_New"
    If ReadWrite Then
        DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, , acMenuVer70
    End If
    DoCmd.Close acForm, stDocName
End Sub
 

Am I making this harder than it needs to be or something?

[NeoPa]

SueB:
Am I making this harder than it needs to be or something?

Very possibly Sue, but interesting nevertheless :-D

Trying to follow the logic, I keep coming up with bits that don't seem to make sense.

Certainly, the standard effect of updating the value and saving it would be to move to another (New) record. Just as you describe. Also, calling DoCmd.Close, as you do in line #6 (Well done for clearly identifying where the problem occurs by the way - So much easier to work with), from within form closing code anyway, would be expected to throw up this error message (It doesn't make sense to call for the form to close when it's already in the process of closing. If it allowed you to do that the code would process through what it had already done again, eventually ending up in an interminable loop).

I would guess that moving your line #6 to the procedure behind the hidden command button may be what you need, but I would need to see the whole module (I don't imagine it's very large) to know for sure.

[sueb]

NeoPa, I tried moving that line to the hidden command button, but I couldn't seem to find the right event. Open to me are:

OnEnter/Exit
OnGotFocus/LostFocus
OnClick/DblClick
OnMouseDown/Move/Up
OnKeyDown/Up/Press

I tried the close command in each of the first four events (the window doesn't close, and nothing else happens either, except for the addition of the new record), and the others don't make sense to me.

Here's the code for that form:

Option Compare Database
 
Private Sub ChartNum_AfterUpdate()
On Error GoTo err_ChartNum_AfterUpdate
    DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, , acMenuVer70
exit_ChartNum_AfterUpdate:
    Exit Sub
err_ChartNum_AfterUpdate:
    MsgBox Err.Description
    Resume exit_ChartNum_AfterUpdate
End Sub
 
Private Sub Form_Close()
    Requery_Open_Forms
End Sub
 
Private Sub Open_New_Patient_Overview_LostFocus()
    stDocName = "Patients_Add_New"
    DoCmd.Close acForm, stDocName
End Sub
 

And really, I can't believe this is this difficult. I just want to keep the user from modifying the chart number, while still allowing her to add new ones. If you have some simpler way to achieve this, I'd switch in a heartbeat!

(oh, and the requery doesn't, but i'll address that in a separate question later)

[NeoPa]

NeoPa:
I would guess that moving your line #6 to the procedure behind the hidden command button may be what you need

I guess this is what you're referring to Sue. The event you would be looking for then is the OnClick event of the Command Button control itself. The code would look somewhat similar to the following (assuming the name of your hidden control is cmdHidden) :

Private Sub cmdHidden_Click()
    Call DoCmd.Close
End Sub

BTW. Your posted module tells me you really should look at Require Variable Declaration. This is very important for helping you to avoid easily avoidable errors.

I expect the example code I included in the procedure is actually all you'll need.

[sueb]

Okay, NeoPa, I put that in the OnClick event, but the window still did not close.

(And thanks for the tip about Require Variable Declaration. This database already had this option set, but one of the other databases I maintain did not, so I set it in that one.)

[NeoPa]

If I remember correctly, you are simply hitting <Enter> after entering the value in the TextBox control of the form. In this case I would expect the event procedure to fire. The only thing I can think of that might be causing it to fail is if you haven't set the value of the OnClick property of the hidden Command Button (cmdHidden) to the string "[Event Procedure]". This can be done easily from the Properties pane when editing the control on the form.

[sueb]

No, the OnClick property is set to "Event Procedure". But is it really true that hitting <Enter> is going to be interpreted as a "click" for this button? Is that because the button is the .Default? Is there any check on that, for instance, would the form give you an error if more than one button were set to be the default? Or is that something that Access could handle?

[NeoPa]

SueB:
No, the OnClick property is set to "Event Procedure".

I assume you mean "[Event Procedure]". This is the default that is set for you from the drop-down list.

SueB:
But is it really true that hitting <Enter> is going to be interpreted as a "click" for this button?

Yes. Indeed it is.

SueB:
Is that because the button is the .Default?

Yes. Absolutely.

SueB:
Is there any check on that, for instance, would the form give you an error if more than one button were set to be the default? Or is that something that Access could handle?

It can never happen. Access automatically resets the .Default property of every other control to False whenever any control is set as the default.

I assume the process is still not working as you expect? If so, you can attach a sanitised copy for me to look at if you like. I'll copy below some instructions for ensuring it's sanitised :

When attaching your work please follow the following steps first :

1. Remove anything not relevant to the problem. This is not necessary in all circumstances but some databases can be very bulky and some things do not effect the actual problem at all.
2. Likewise, not entirely necessary in all cases, but consider saving your database in a version not later than 2003 as many of our experts don't use Access 2007. Largely they don't want to, but some also don't have access to it. Personally I will wait until I'm forced to before using it.
3. If the process depends on any linked tables then make local copies in your database to replace the linked tables.
4. If the database includes any code, ensure that all modules are set to Option Explicit (See Require Variable Declaration).
5. If you've done anything in steps 1 to 4 then make sure that the problem you're experiencing is still evident in the updated version.
6. Compile the database (From the Visual Basic Editor select Debug / Compile {Project Name}).
7. Compact the database (Tools / Database Utilities / Compact and Repair Database...).
8. Compress the database into a ZIP file.
9. When posting, scroll down the page and select Manage Attachments (Pressing on that leads you to a page where you can add or remove your attachments. It also lists the maximum file sizes for each of the allowed file types.) and add this new ZIP file.

It's also a good idea to include some instructions that enable us to find the issue you'd like help with. Maybe some instructions of what to select, click on, enter etc that ensures we'll see what you see and have the same problems.

[sueb]

NeoPa, I'll certainly do that today! I have an all-morning meeting starting in a few, so it will be later today.

So I was interested to see that one of your instructions was to compile the database. I've been wondering whether there wasn't something like that in Access, mostly because my collection management seems to have gotten so very slow. Would compiling everything make it run faster? Or is it a different thing from the compiling I'm used to?

[NeoPa]

VBA is a strange animal Sue. It's technically referred to as semi-compiled. That is to say that there is an element of pre-processing involved, yet there is an interpretation element too. The compilation element is particularly useful for catching the more obvious errors. By obvious I suppose I really only mean those which can be checked around known rules. It can't detect all problems, but only those that break the fundamental rules.

It's also interesting to note that as each separate line of code is interpreted as it's run too, there is an interpretation overhead in time for all VBA code.

Actually, it seems that Access compiles the code automatically in some certain circumstances, but as I'm not exactly which they are, and belt and braces doesn't hurt, I always recommend (and do myself) compiling a project manually.

[sueb]

Okay, NeoPa, I'm attaching my database's front and back ends in a single zip. The simplest way to reproduce this is to execute the query "ACE_Volumes" from the database window. I hope you are able to find out something!

Thanks for your help.

[NeoPa]

I think your instructions were aimed at another problem Sue. I've responded to that in the relevant thread (after moving it from here where I pasted it in error, before I realised this wasn't the correct thread). I managed to find the problem in your database that is described in this thread, and my findings are detailed below.

The more modern method of calling the Save for the record is :

Call DoCmd.RunCommand(Command:=acCmdSaveRecord)

The .DoMenuItem form has been superceded (It was always very kludgy and hard to read - as well as hard to write of course).

I found the code in your ChartNum_AfterUpdate() procedure when looking at the form [Patients_Add_New]. This is called from the form [Patient_IUR_Overview].

The changes I made to fix your problem were :

1. The form itself was saved with a value in the .Filter property. This was removed.
2. The control [ChartNum] was updated to change properties :
   1. .EnterKeyBehavior set from "New Line in Field" to "Default". This was ensuring the default control on the form was not being triggered as it would otherwise have been.
   2. .ScrollBars set from "Vertical" to "None". This just looked wrong.

It seemed to work fine after I'd made those changes. I assume it's what you're after.

[sueb]

You're right, NeoPa, I posted in the wrong thread--sorry!

Also, these changes did correct the behaviors I was asking about, but I'm going to ask something further (and I apologize for the length of this post--tell me if I should actually put this in another thread).

[NeoPa]

There are a few issues with this Sue. Let me explain :

1. As you say, it should be in its own, separate, thread. I will do that for you.
2. The database has a reference to a Novell library for picking dates. This is not generally available and I don't have a copy, so I can't even run your database now, as things stand. I would need you to attach the file C:\Novell\GroupWise\gwdpk.ocx. It would probably need to be zipped to be attachable.
3. The scope of the question is too broad. It rather asks me to do the design and implementation for your project. I'd like to be able to help, but I'd need to do it in a more focused way. I could possibly post concepts that you would need to include in your database. Otherwise, it's just too much of an ask.

Looking again at the new question (which can now be found at Add Items Not In List), I may be able to post something. I'll see what I can do anyway, and we can take it from there.

[sueb]

Thanks for all this, NeoPa--I'll move to the new thread for the rest of the discussion, but here's the library reference for you. (I actually had an idea last night about simplifying this--I'll try it out and post what I find out in the new thread.)

[NeoPa]

Sweet. I imported into the same folder as the database and it recognised it without my having to fix anything :-)

[sueb]

Great! It's so rare for something to just slip into place, isn't it?