Subform Controls Disappear When Edits/Deletions/Additions = No

[BASSPU03]

I'm using Access 2003 on a Windows XP O/S.

I've made two copies of Form1 so that there're are 3 forms:

1) Form1
2) Form2
3) Form3

Essentially, they all write to the same Table1.

Each of these forms is reached by clicking one of three links on my switchboard:

1) New Record --> Form1
2) View All Records --> Form2
3) Edit Records --> Form3

I thought I could just make the following modifications to each form's properties and make each form function in a different way:

1) Set Form1 to "Data Entry" and remove navigation controls.
2) Set Form2's Recordset Type = Snapshot OR Edits/Deletions/Additions = No.
3) Leave Form3's properties alone. Possibly add a record search feature so that only one record is edited at a time and maybe set a user/password so that records aren't edited without authorization.

Alas, I've got a few problems.

A) Even though I set Form1 to "Data Entry," the first record is totally blank (i.e. no controls show up at all). There are no records in the underlying Table1. Now, I know that controls usually disappear when there are no records in the underlying table and/or the properties are set to Edits/Additions/Deletions = No and/or Recordset Type = Snapshot.

Question: What's the best way to set up a data entry form and/or to keep the controls visible regardless of whether there are records in the underlying table?

B) What's a good way to set up an editable form so that access to the records isn't too liberal? Is there a good user/password code that I can use? Is there a way to set up a query or search parameter so that only a single record is opened according to the AutoNumber (i.e. the file number) that the user wants?


Thanks in advance for all your help.
-Benny

[BASSPU03]

Question: What's the best way to set up a data entry form and/or to keep the controls visible regardless of whether there are records in the underlying table?

OK, so I've managed some headway into this problem and this is how I worked around the "Edits/Deletions/Additions" and "snapshot" dilemmas.

The only way I know to keep Form1 as a data entry form without sacrificing the visibility of the controls is to not mess with "Edits/Deletions/Additions" and Recordset Type properties at all. Instead, I just set the Data Entry property to "Yes" and removed the navigation controls. Since the navigation controls are gone, it's now not possible to simply click the " >* " button for a new record. So, after one is done entering data for one record, he/she can click a command button labeled "Add New Record" that essentially closes Form1 and opens it again, but it's now refreshed. The code I used for it is essentially the same code I used to close my switchboard when I clicked "OK" to move on to the next switchboard "Main Menu." Here's that code:

Private Sub cmdNewRecord_Click()
On Error GoTo Err_cmdNewRecord_Click
 
    Dim stDocName As String
    Dim stLinkCriteria As String
 
    DoCmd.Close acForm, "frmNewResource"
    stDocName = "frmNewResource"
    DoCmd.OpenForm stDocName, , , stLinkCriteria
 
Exit_cmdNewRecord_Click:
    Exit Sub
 
Err_cmdNewRecord_Click:
    MsgBox Err.Description
    Resume Exit_cmdNewRecord_Click
 
End Sub
 

I put:

DoCmd.Close acForm, "frmNewResource"
 

before

stDocName = "frmNewResource"
DoCmd.OpenForm stDocName, , , stLinkCriteria
 

instead of after it like I did for the switchboard and it works just as if I were opening up a blank new form.

I'm still stuck on finding a code that'll allow me to pull up an individual record by AutoNumber for editing purposes. Any help would be greatly appreciated.

Best,
Benny

[missinglinq]

I’ve got to tell you, Benny, I’ve gotten a headache from trying to follow all the ins and outs of what you’ve already tried in your database! So I’m going to start off with some very basic advice: Keep It Simple! When tackling a relatively big job like this, take a pen and apiece of paper, and write down a very simple outline of the problem. In this case you need:

1. SwitchBoard Form
2. New Record --> Form1
3. View All Records --> Form2
4. Edit Records --> Form3

Next, and this is the important part, take the problems on one at a time! Don’t go harrying off in three or four different directions, working first on one form, then another, then a third, never getting any of them to work properly, and becoming mired down more and more as you go along. This particular problem isn’t, believe it or not, all that complicated!

To begin with, you don’t need four forms, you only need two! A SwitchBoard form and a form to display your records. Stop and think about it; what you’ve called Form1, Form2 and Form3 are really the same form! They all hold the same data! You simply want the form to handle that data in different ways, depending on circumstances, so that’s what you should do, have one form but make it operate in different ways, depending on your needs. I’ll give you most of what you’ll need to accomplish this task, only holding off, for now, on the question of limiting who has the ability to edit/delete records. After you get everything else up and working, you can tackle that.

While it’s true that you can control whether records can be added or edited or deleted or even viewed, thru the Properties box for a given form, you can also do this by the manner in which you open the form, and that’s what I’ll demonstrate here.

The SwitchBoard form is where you’ll navigate, not between three almost identical forms, but rather between the three instances of the same form. Let’s start with this form, frmSwitchBoard:

You’ll need three command buttons to open the data form in the appropriate mode, cmdNewRecord, cmdViewAllRecor ds and cmdEditRecords.Here’s the code for these buttons.

This button will open the data form so that new records can be entered. You can add one, or more than one, new record, but you can't navigate to an existing record! Opened in this mode (acFormAdd) the Access nav buttons will only move to a new record. After frmNewResource is opened frmSwitchBoard is closed.

Private Sub cmdNewRecord_Click()
On Error GoTo Err_cmdNewRecord_Click
 
    Dim stDocName As String
 
    stDocName = "frmNewResource"
    DoCmd.OpenForm stDocName, , , , acFormAdd
    DoCmd.Close acForm, "frmSwitchBoard"
 
Exit_cmdNewRecord_Click:
    Exit Sub
 
Err_cmdNewRecord_Click:
    MsgBox Err.Description
    Resume Exit_cmdNewRecord_Click
 
End Sub

This button will open the form in ReadOnly mode. You can view all records but you can't add/edit/delete records; Access disables these options when the form is opened using acFormReadOnly.

Private Sub cmdViewAllRecords_Click()
On Error GoTo Err_cmdViewAllRecords_Click
 
    Dim stDocName As String
    Dim stLinkCriteria As String
 
    stDocName = "frmNewResource"
    DoCmd.OpenForm stDocName, , , , acFormReadOnly
    DoCmd.Close acForm, "frmSwitchBoard"
 
Exit_cmdViewAllRecords_Click:
    Exit Sub
 
Err_cmdViewAllRecords_Click:
    MsgBox Err.Description
    Resume Exit_cmdViewAllRecords_Click
 
End Sub

This final button allows the records to be edited or deleted. The OpenArgs parameter ("Edit Only") is used in this OpenForm command. When accessed in the Form_Load event of frmNewResource, it tells Access not to allow new records to be added and allows the combobox cboResourceID to be visible. This dropdown box will be used to go to a given record for editing/deletion, and will only be visible when the form is opened in edit mode..

Private Sub cmdEditRecords_Click()
On Error GoTo Err_cmdEditRecords_Click
 
    Dim stDocName As String
    Dim stLinkCriteria As String
 
    stDocName = "frmNewResource"
    DoCmd.OpenForm stDocName, , , , , , "Edit Only"
    DoCmd.Close acForm, "frmSwitchBoard"
 
Exit_cmdEditRecords_Click:
    Exit Sub
 
Err_cmdEditRecords_Click:
    MsgBox Err.Description
    Resume Exit_cmdEditRecords_Click
 
End Sub
 

Now, for your data form, frmNewResource:

First, if you haven't already done so, create your data form frmNewResource based on your table/query, including all the fields you want displayed. Leave all of the form's properties at their Default settings! Don't mess around with the AllowAdditons, AllowDeletions, AllowEdits and Data Entry; the mode used to open the form will take care of these things. Then simply:


Add a combo box to your form. The Combobox Wizard will pop up

Select "Find a record based on the value I selected in my combobox."

From the table/query the form is based on, click on the field you're searching by (your ID field) to move it to the right side.

Hit Next.

Size the column appropriately.

Hit Next.

Name the combobox cboResourceID.

Hit Finish.

Now you can drop the combobox down and scroll down to the item to search by, or you can start to enter the item, and the combobox will "autofill" as you type. Hit <Enter> and the record will be retrieved.

This code, thru the use of OpenArgs, tells Access to not allow new records to be added when the form is opened for editing/deleting of records. It also allows the combobox we made, which will be used to retrieve a given record, to be visible.

Private Sub Form_Load()
If Len(Nz(Me.OpenArgs, "")) > 0 And Me.OpenArgs = "Edit Only" Then
    Me.cboResourceID.Visible = True
    Me.AllowAdditions = False
Else
    Me.cboResourceID.Visible = False
End If
End Sub
 

Give this a try now, keeping a few things in mind. First, try to get the code working as it's given here. If you have problems, post back here telling us about it. Don't go changing this, changing that willy nilly and then posting back when it doesn't work; this will just end up confusing you and the people here who are trying to help you. This is always a good policy when trying to implement code someone's posted. The exception, of course, would be when you're simply replacing generic control names with your own actual names.


Next, create your frmSwitchBoard with its buttons and code and your frmNewResource with it's combobox.


Finally, test things one at a time. Make sure your data form is behaving properly. Make sure the combobox is working correctly, retrieving records as it should. And finally, test out each form opening mode to make sure that they're behaving properly.



I've spent a fair amount of time hacking this problem, and it's all working on my end, but sometimes things do get copied/pasted incorrectly going from Access to here on the forum. So if you have any problems, post back here and be patient. I'll be checking back fairly frequently tomorrow (well, today actually; it's about 0400 hours here) and I'm sure other people will be looking in as well.


Good luck!


Linq ;0)>

[BASSPU03]

All but one thing is working fantastically as far as I can tell. My most sincere gratitude....

First the problem, then the optional read:

PROBLEM

Now you can drop the combobox down and scroll down to the item to search by, or you can start to enter the item, and the combobox will "autofill" as you type. Hit <Enter> and the record will be retrieved.

This works very well, but, after hitting <Enter>, I can't get the record to change. I can tell that it doesn't change at all because a bound AutoNumber textbox in the upper right-hand corner of the window stays at '85,' for example, even if I attempt to navigate to '86.' This AutoNumber, although repetitive in the "Edit Records" version of my form, is useful when the search ComboBox is invisible in the "Create New Record" and "View All Records" versions.


OPTIONAL READ

Stop and think about it; what you’ve called Form1, Form2 and Form3 are really the same form! They all hold the same data! You simply want the form to handle that data in different ways, depending on circumstances, so that’s what you should do, have one form but make it operate in different ways, depending on your needs.

My mind thinks this way but it doesn't possess the Access knowledge to get it to function accordingly. : ) So, instead, I come up with (usually inefficient) workarounds to keep progressing. My time on this project is very limited, so I'm trying to keep going without having to post every single question. And, please, let's not get started on normalization!

First, try to get the code working as it's given here. If you have problems, post back here telling us about it. Don't go changing this, changing that willy nilly and then posting back when it doesn't work; this will just end up confusing you and the people here who are trying to help you.

According to your codes, my switchboard kept closing after clicking one of the three linking buttons. I'd rather keep the switchboard open underneath. I know you said not to change it, but I've learned enough to know that removing the DoCmd.Close line will keep my switchboard open. After removing it, it works just fine. Please don't hate me!

I've spent a fair amount of time hacking this problem, and it's all working on my end, but sometimes things do get copied/pasted incorrectly going from Access to here on the forum. So if you have any problems, post back here and be patient.

You should be paid by commission. Really. I can't thank you enough for actually reproducing my scenario and posting very thorough codes and instructions. I am very grateful, but please don't give yourself headaches over my issues!

I will make future posts more concise and limit them to single problems. Trust me: I don't want to be the guy that's lambasted for improper forum etiquette. These types of forums are a bit more demanding than most and it takes some getting used to. Nonetheless, I understand the purpose. Not only is this my first forum with strict guidelines, but it's also the first one whose rules are actually enforced.

If there's any place I can offer positive feedback for your efforts, please let me know.

Sincerely,
Benny

[BASSPU03]

I don't mean to sound impatient, but I haven't heard anything in a week. Missinglinq: Thanks again for the combobox code to search editable records by keyword. On a slightly-related note, I commented that I couldn't get the combobox search function to change records. I found a code that does the trick:

Private Sub cboResourceID_Change()
    Dim strSearch As String
    strSearch = Me.cboResourceID 'whatever the name of your combo is
    Me.AutoNumber.SetFocus
    DoCmd.FindRecord strSearch, acEntire, , acSearchAll, , acCurrent
End Sub
 

It's in the OnChange event and works like a charm.

However, there's a problem. I noticed that after I entered all of your codes, my print preview feature only successfully previews the first record. When I change records and click Print Preview, the corresponding report appears blank (i.e. with empty fields). Any idea why? Could it be because of the edit limitations your code imposes on the form? This is the code for my print preview button:

Private Sub cmdPrintPreview_Click()
    Dim strWhere As String
 
    If Me.Dirty Then    'Save any edits.
        Me.Dirty = False
    End If
 
    If Me.NewRecord Then 'Check there is a record to print
        MsgBox "Select a Record to print"
    Else
        strWhere = "[AutoNumber] = " & Me.AutoNumber
        DoCmd.OpenReport "Administration", acViewPreview, , strWhere
    End If
End Sub
 

Thanks.

[missinglinq]

Once again we need to work on one problem at a time! In fact, I think I'm going to split off the problem of having a main form the editable with read-only subforms. That way the title will reflect the current problem more acurately.

Try modifying this code:

 Private Sub cmdPrintPreview_Click()
     Dim strWhere As String
 
     If Me.Dirty Then    'Save any edits.
         Me.Dirty = False
     End If
 
     If Me.NewRecord Then 'Check there is a record to print
         MsgBox "Select a Record to print"
     Else
         strWhere = "[AutoNumber] = " & Me.AutoNumber
         DoCmd.OpenReport "Administration", acViewPreview, , strWhere
     End If
 End Sub
 

to this:

 Private Sub cmdPrintPreview_Click()
     Dim strWhere As String
 
     If Me.Dirty Then    'Save any edits.
         Me.Dirty = False
     End If
 
     strWhere = "[AutoNumber] = " & Me.AutoNumber
         DoCmd.OpenReport "Administration", acViewPreview, , strWhere
     End If
 End Sub
 

You don't need to check for a new record with

If Me.NewRecord

because there won't be any! If there was a new record, it's no longer new, because you saved it with

If Me.Dirty Then 'Save any edits.
Me.Dirty = False
End If

So try the code with this and see what happens. And as I said, I'm going to split off the last problem.

Linq ;0)>

[BASSPU03]

I tried the code but it still brought up an empty report. Funny thing is that I tried creating a form print preview command button using the wizard and the report still came back empty (i.e. report's there, but the fields are empty, even though the record isn't).

I think this starting happening when I added the New Record, View All Records, and Edit Records codes that you offered earlier in this thread.

[sierra7]

I tried the code but it still brought up an empty report. Funny thing is that I tried creating a form print preview command button using the wizard and the report still came back empty (i.e. report's there, but the fields are empty, even though the record isn't).

I think this starting happening when I added the New Record, View All Records, and Edit Records codes that you offered earlier in this thread.

Is you Print Preview still blank if you navigate back to a previous record ?

If not then add a DoCmd.RunComman d acCmdSaveRecord at Line 3 to force a 'save' because it sounds as though the report is not finding any data.

You could also 'comment out' the criteria (put an apostrophy after acViewPreview and before the comma) which should list all your data, just to test the report is working. If this would result in too much date then hard-code the criteria to a particular value or range.

Interesting reading otherwise!

[BASSPU03]

Sierra7, thank you so much for your suggestions! Unfortunately, none of them worked. However, I'm optimistic because I created a new form using the wizard and the report is working again, even when I change to a new record. I think I may have tinkered a bit too much with the DB and the report no longer reflected the tables' values. I'll keep my fingers crossed and will post back if it comes up again. Thanks again!