Need to Add and Delete records in subform

[Dinah Beres]

I am an Access beginner trying to get up to speed.
I have a form (Person data) with subform (task data)
I need to be able to (1) create and (2) delete task records on the subform.
I used DoCmd.RunCommand acCmdDeleteRecord successfully on the Person form. This doesn't work on the subform.
I have not been able to find a method that will create a new record.
(3) I have a double combo box search above the task subform. The first combo box correctly allows the user to select the person, and the second combo box does correctly present the task version numbers for that person. However it presents the version number from EVERY task. A test record has 3 tasks in version 1, 2 in version 2, and 5 in version 3, resulting in the 2nd combo box presenting the user with 1,1,1,2,2,3,3,3,3,3 instead of 1,2,3
How do I get it to select DISTINCT?
Any suggestions would be gratefullly appreciated.

 

[dgm]

U can use DoCmd.RunCommand acCmdDeleteRecord in the subform, but the button must be in the subform as well...
I put mine in the subforms footer.

For a new record, just use :
docmd.GoToRecord acDataForm,,acNewRec

For distinct values in your combo box, go into your query and select properties from the View menu.
Where it says 'Unique Values' in the properties window, enter Yes...

Hope that helps a bit.

 

[Dinah Beres]

Thanks for your prompt reply.
The buttons are in the subform (I did think of that yesterday).
I copied them from the old location to the new one. Was that a mistake? The database has been completely closed since then, would that remove a lingering link to the wrong table?

on the delete: MSAccess error 2046 "The command or action 'DeleteRecord' isn't available now"

On the Add record, run time error 2493 "This action requires an Object Name Argument"

On the query, distinct values is already selected. Since this query is based on another one, perhaps that is a problem?

Thanking you in advance.

 

[dgm]

No, the delete record command works on the currently selected record (in the same form as the button). U usually get the "The command or action 'DeleteRecord' isn't available now" error when there is no current record selected (or the current record is a new record?) or if your recordset isn't updatable. Can u add/edit existing records?

Sorry i made a mistake on the new record. It should be:
docmd.GoToRecord ,,acNewRec

I'm not sure why your distinct values isn't working. Being from another query shouldn't matter. How many fields are in the combo box? Maybe u could post the sql for each combo, might be able to spot something then.

 

[Dinah Beres]

need add/delete record

A picture is worth a thousand words, so I have enclosed a stripped version of the database with the tables/forms that are giving me fits. This gives you the combo box queries, and anything you forgot to ask and need to.

Problem0: Name select combo now gives runtime error 3021 No current record. And adds selected person’s ID as name on new record, which gives error 13 type mismatch. This was working yesterday. Microsoft's solution was to delete the combo box and do it again. I'd like to know why the name search combo box keeps going out on me and what I can do to prevent it.
Problem1: Add Task, may work, can't test because of other problems.
Problem2: Delete Task, you are on target: I can't edit the tasks (#4), so I can't select them and therefore can't delete them.
Problem3: Add Person not working correctly yet.
Problem4: Can’t edit tasks in frmPlan (see #2)

Thank you for taking the time to help out. Trying to get up to speed on something as complex as Access is rather wearing.

 

[Dinah Beres]

add/delete

I have to leave the office now, will be gone for a week. I'll pick up this thread when I return on Nov 16. Thanks.

 

[dgm]

Ok, there are a few problems there...

With your name search combobox on frmPerson, you were trying to use it to search the form as well as store the name in the table. That's why it added a new record everytime u selected a name. Remove the [name] field from the combo box's control source.

In the combobox's rowsource put SELECT [tblPerson].[ID], [tblPerson].[Name] FROM tblPerson ORDER BY [tblPerson].[Name]; or go into the query designer and select ID and Name... that's all u need in there.

Also, under the format tab of the combobox's property window, change the Column Count setting to 2 and the column width setting to 0. What this does is displays the persons name, but actually selects the ID (which is now hidden) and uses that to search the form with (which is what it was trying to do, hence the type mismatch).

And lastly, under the forms property window, change the Data entry setting (under the data tab) from Yes to No. Data entry set to yes, only allows new records to be entered. So your combobox was looking for records that weren't there. That should also fix your add and delete problems...

For frmPlan, the problem is with the query used in your subForm "qryVersion". It's set to display distinct records only, the problem with that is that query is then not updatable, so change the Unique Values setting to No. Also remove the criteria u have for the name field. This isn't required as the Child/Master links setting accomplishes this for u.

You should set up the combobox on this form the same as on frmPerson.

I think i remembered everything, but if u have trouble let me know.

Dave

 

[Dinah Beres]

debugging database

Thanks, Dave. Your assistance is greatly appreciated.
Sorry about the long delay. It's been a difficult time for me.
I've implemented the changes you suggested.
The search now works on frmPerson.
The delete button works correctly.

Problems to go:
1. The person search does NOT work on frmPlan.
I've checked the combo box carefully, it appears to match the working combo box on frmPerson so I think the problem is in the interaction with the version (it should come up with the active verson of the person specified. [After this simple version is delivered, I need to add the capability of selecting a version from the selected person's list of versions, but not now]) and perhaps the sfrmPlan setup.
2. The Add Person button of frmPerson doesn't work.
3. I'd like to display the JobSeriesTitle that corresponds to the JobSeries field in frmPerson. This is nice but not necessary.
I am again enclosing a copy as the easiest way to 'describe' the problem.

 

[dgm]

1. The problem lies with your subForm, sfrmPlan.
Change the Data Entry property for the form to No.

I'm not sure what your trying to do with version. Your version combo does nothing but select a version form the list at the moment. It seems as if your doubling up a lot there. U have version and active fields in tblPerformance and verActive and verLatest in tblPerson. I'm sure there's something not quite right there, but without knowing what your doing, it's hard to say.
Which is not to say it won't work, it's just not optimal, lol.


2. The add button worked for me, however now u have the name select combo as an unbound field, u will need another bound field to enter the new name.

3. Easy enough. U just have to create a query to use for your form instead of linking it directly to the table. In the query, link tblPerson and tblJobSeries via the JobSeries field and include JobSeriesTitle in the query results.

Well try that out anyway, and if something still doesn't work just post again.

Dave

 

[Dinah Beres]

add and delete

Thanks. I appreciate your prompt reply, and have implemented your suggestions. I think that one problem was that I didn't close Access and re-open after making changes. frmPlan worked for me after doing that, and without changing anything.

You are correct that the version is unnecessay in frmPlan. This first release will just show the active version. The next release will allow the user to select another version and make it the active version. I have thought that through carefully, but I still expect some changes will be necessary when I actually start implementing it. Need to get this show on the road first.

Problems remaining:
1) frmPerson opens with the new bound name field filled with whatever name I entered last. How can I blank this, or better yet, have it invisible until the Add Person button is clicked.

2) basing frmPerson on a query is causing a bug with the combo box name search. I added ID to the query. The combo box search brings up "walks" correctly, but not "Caveman" Any way I can have both? Or should JobSeriesTitle be a sub-form?

I will be out of the office Thurs and Fri, back on Monday for a full week.

 

[dgm]

I'm not sure what's happening there.
I'll post your sample back so u can have a look at what i did.

Dave

 

[Dinah Beres]

add/delete problem

Thanks. I like your changes to frmPerson, and have implemented them.
Add Person works well.
Delete Person works, but I don't know how to get the deleted person's name into the message box. Here's the code.
On Error GoTo ErrorHandler 'not an error if user cancels delete
Dim nametemp As String
Dim rs As Object
Set rs = Me.Recordset.Clone
rs.FindFirst "[ID] = " & Str(Me![cboNameSelect])
nametemp = rs.Bookmark
DoCmd.RunCommand acCmdDeleteRecord
MsgBox "You have deleted " & nametemp
DoCmd.Close 'defaults work!
DoCmd.OpenForm "Switchboard"
Exit Sub
I cribbed this from the search routine, as I don't have any examples like this.

Add Task and Delete Task don't work, nor can I change the data in a task. Your copy has this flaw, so I'm not enclosing another copy.

 

[dgm]

Try:
nametemp = me!name

btw, name is not the best word to use as a field in a table, as it's used by access a lot. For example, me.name will return the name of the form. Access can get confused sometimes, so it's better to add something to the front of your field names. Like if in your tblPerson, you could have:
perName, perCode, perSuper, etc

Which, now that i look at it, could be the problem with your task subform... I'll have a closer look now and get back to u.

Dave

 

[Dinah Beres]

Thanks! Worked like a charm. Access did give me the name of the table yesterday, as you thought it might. Do I need these lines? I'm trying to make sure the sub gets the right record.
Dim rs As Object
Set rs = Me.Recordset.Clone
rs.FindFirst "[ID] = " & Str(Me![cboNameSelect])

I've chosen "NameFull" as it is lastname and firstname.
I may implement your suggested naming scheme later, as it tags which table a field is in, a very good idea. I've done enough maintenance programming to know how important every clue is when you are trying to trace a problem.

I also got the JobSeriesTitle to display correctly by basing the form on a query as you suggested, but the name-finding combo box got disconnected in the process and the name search doesn't work. Is there an easy fix for that?

 

[Dinah Beres]

The name change has generated some fallout.

the following line in frmPerson works, but
rs.FindFirst "[ID] = " & Str(Me![cboNameSelect])
this is the version that was in frmPlan
rs.FindFirst "[ID] = ' " & Me![cboNameSelect] & " ' "

I'm enclosing a copy as I need to know what followthrough I have missed.

Still unresolved,
FrmPlan doesn't often show tasks in sfrmPlan
Still can't edit/add/delete tasks in sfrmPlan.

New:
frmEval doesn't bring up sfrmEval.
I've not enclosed this before, as it was working

 

[dgm]

Your query qryVersion has reverted back to a previous version i think, lol.

Change it's unique value property to No and
remove the criteria from nameFull.

That should fix frmPlan i think.


In your frmEval subForm properties, the Master Link is still set to name, should be now nameFull. Access usually fixes these for u but not always i guess.


For your delete button:

Private Sub btnDeletePerson_Click()
    On Error GoTo ErrorHandler 'not an error if user cancels delete
    Dim nametemp As String
    Dim rs As Object
    Set rs = Me.Recordset.Clone
    rs.FindFirst "[ID] = " & Str(Me![cboNameSelect])
    nametemp = Me!NameFull
    DoCmd.RunCommand acCmdDeleteRecord
    MsgBox "You have deleted " & nametemp
    DoCmd.Close 'defaults work!
    DoCmd.OpenForm "Switchboard"
    Exit Sub

U don't need all that. The Delete record command deletes the current record, so u don't have to specify anything.
Try this, this is how i usually delete records.

Private Sub btnDeletePerson_Click()
On Error GoTo ErrorHandler

    DoCmd.SetWarnings False
    If Not IsNull(Me!NameFull) Then
        If MsgBox("Delete " & Me!NameFull & "?", vbYesNo, "Delete?") = vbYes Then
            DoCmd.RunCommand acCmdDeleteRecord
            DoCmd.Close 'defaults work!
            DoCmd.OpenForm "Switchboard"
        End If
    End If
    
exitHandler:
    DoCmd.SetWarnings True
    Exit Sub
    
ErrorHandler:
    Msg = "Error # " & Str(Err.Number) & " was generated by " _
        & Err.Source & Chr(13) & Err.Description
    MsgBox Msg, , "Error", Err.HelpFile, Err.HelpContext
    Resume exitHandler
End Sub

I don't really like the built in access dialogs. They can be a bit confusing for users sometimes.

Also, u might want to look at cascading deletes in your relationships. For example, if u say deleted Walks on Water from frmPerson, there would still be records in your other tables for him/her? lol

Something to think about anyway.
If u need somthing there explained, just let me know.

Dave

 

[Dinah Beres]

Thanks. I like your delete routine.
The excess clutter in mine was to be sure the message box got the right name for the "you just deleted ....." message.

frmPlan is working.
I went back to an older version and updated it, eliminating some problems. I still don't know what causes the fix to A to change B

There's still a disconnect on frmEval.
The subform now has NameFull. It's connected to qryActive, which I'm not sure is necessary, as I have Active = yes on filter.

BTW, what is "lol"?

 

[dgm]

Change this
rs.FindFirst "[ID] = '" & Me![cboEvalNameSelect] & "'"
to this
rs.FindFirst "[ID] = " & Me![cboEvalNameSelect]
The extra ' ' are only required for text.
Or to keep all your code the same use:
rs.FindFirst "[ID] = " & Str(Me![cboEvalNameSelect])

I'm not sure if your filter is doing anything as u would need to add another command in your code to turn it on:
Me.Filter = "Active = 'Yes'"
Me.FilterOn = True
I would just leave it up to the query. That's usually the best way.


lol = laugh out loud

Dave

 

[Dinah Beres]

Thanks! Suggestion implemented. Worked.
Lol, I need to regain my balance and sense of humor. Glad you have one. If Access reverted to an old version, it did so without my knowledge or consent.
Amazing, except for the following minor problems, the database actually works!

1) I solved the Job Series Title problem with a sub-form containing just that one field, query-linked to Job Series.
It's only problem is that if you change the Job series NUMBER, the title doesn't change. You must navigate away from and back to that person to get the title that matches the new number. How can I fix this? Put a requery in somewhere?

2) "Add Task" probably doesn't. Since there is a blank task at the bottom of frmPlan, I can probably remove the button and change the procedure, but I'd like to do it 'right'. I'd like Add Task to add the next available Performance Task Number, and set Version to the current version and Active = yes. How can I do this?

3) The header of sfrmPlan, which contains Add Task and Delete Task buttons, scrolls out of sight for long records. Is there any way of keeping it fixed in place and visible?

4) when I hit Add Person, if the Person has a job series number that isn't in the list (I got the list of job series numbers that our workgroup currently contains, but someone could be hired or transferred in with a job code that isn't on the list, although unlikely) you can type it in but it doesn't go into tblJobSeries. Access can bring up a window that says "This field is defined to contain only certain values. Allow this value?" If you say yes, you can add this value to the tblJobSeries and then go back and it's there to select. How do I get Access to do this?

Tomorrow is our Thanksgiving Holiday, so I will skip a day and be back on Friday. (One of the things I am very thankful for is your help!)

 

[dgm]

Well u have to keep your sense of humour, otherwise you'll go crazy!

1. It's funny how u sometimes forget things. I just realised that the correct, and most likely easiest way to do the job series title is with a simple combbox. No queries or sub forms involved.

Just create a combobox, with its rowsouce as:
SELECT [tblJobSeries].[JobSeries], [tblJobSeries].[JobSeriesTitle] FROM tblJobSeries;
Make sure in the cobobox's properties u set Column Count to 2 and column widths to 0.
And make the Combo bound to Job Number.
Or just use the wizard, even easier just follow the prompts.

So now u will see the Job Title, but the Job Number is stored in the table, as it is now.

Otherwise i think if u put a requery in the On Change event of the job number, it should work.

2. Ahhh that could be tricky. From what i see, Perf No is based on the person and version? So to get the next number u would have to look at the name of the person and their current version, find the last number used and then add one.

Ok, try this. Create a new query based on tblPerformance.
Add name, version, perfNo and Active. Add -1 to the criteria for active, because u only want to search the active records.
Save that as qryPerfNo, then in your Add new code put:

Private Sub btnAddTask_Click()
    'add new task record
    Dim strCrit As String ' holds the criteria for the look up
    Dim intPerfNo As Integer ' holds the perf No returned
    Dim intVersion As Integer ' holds the current version
    
    intVersion = Me!Version
    strCrit = "[pfrName] = '" & Me!pfrName & "' AND [version] = " & intVersion
    intPerfNo = DMax("[perfNo]", "qryPerfNo", strCrit)
    DoCmd.GoToRecord , , acNewRec
    Me!PerfNo = intPerfNo + 1 ' set the perf no
    Me!Version = intVersion ' set the version
    Me!Active = -1 ' set active to yes
    Me.Requery
End Sub

That should do it.

3. Not that i know of.

4. Ohh that could be a problem with my combobox suggestion? Although a way around that would be to create a new form based on tblJobSeries, and when u double click on the combo it opens this form which allows u to add a new job number, title, etc. Or u could just display all job series (in a continous form) and add a new one at the bottom. When u close the form it requeries the combo.

If u want to follow your suggestion, u need to look at the Not in list event. Oh wait thats only for combobox's... Your job number is currently a text box right? Or have u changed it?

Hopefully i haven't just added to your problems, lol.

Anyway, enjoy your Thanksgiving holiday.

Dave