Add new record using unavailable IDs (1 Viewer)

[NBRJ]

Further to a previous thread... and valuble advice from Minty about being able to have a subform of a continuous form (in the footer)...

I have a continuous form (fLocation) based off a query (Record Source: SELECT * FROM qLocation) which in turn is based off a table (tLocation, which has autonumber PK called IDLocation, txt field called Room, then a bunch of FIDs that link to other tables, and some number fields). The FIDs are the reason for the use of qLocation to make the continuous form has meaningful data displayed.

I then have, in the header, a filter, so I can filter what the continuous form displays.

In the footer I have:

1) Sum text fields that sum up some number fields in the continuous form based on the current filter.

2) a single record view sub form which displays whichever record is selected at the time in the continuous form.

This is called fLocationSubform and it's Record Source is tLocation to enable editing and the FIDs are bound to cboboxes to show meaningful data.

When this subform is in place, the SourceObject is: fLocationSubform, and the Link Master Fields and Link Child Fields are both set to IDLocation.

I can edit a record and have it update the continuous form without issue.

This issue I'm having is with the new record not automatically assigning and unique UNUSED ID.

When I click my cmdNew button which is on the tLocationSubform (and created via the button wizard: Record operations > add new record) it gives me a blank record on the fLocationSubform as you'd expect prior to data entry, with (new) in the IDLocation field. Directly I start to enter data (when the ID gets assigned) it gives me the ID of the currently selected record in the continuous form rather than a new one (yes IDLocation is AutoNumber).

I then obviously get the "The changes you requested to the table were not successful because they would create duplicate values..." message.

Why is it doing this? Why is it not picking up a new, unused ID? And what can I do about it?

I changed the embedded macro that the button wizard creates to this but no joy to preventing the used ID issue:

Private Sub cmdNewRecordV2_Click()

Dim db As DAO.Database
Dim rs As DAO.Recordset

    Set db = CurrentDb
    Set rs = db.OpenRecordset("tLocation", dbOpenDynaset)
    
With rs
    .AddNew
    !FIDBuilding = cboFIDBuilding
    !Room = txtRoom
    !FIDUsage = cboFIDUsage
[COLOR=Green]    'etc[/COLOR]
    .Update
End With
Me.AllowAdditions = False
rs.Close
Set rs = Nothing

End Sub

 

[NBRJ]

It has to be something on the form that's causing this. I went into the table (tLocation) and tried to add a new record and it did the same thing, used a already used ID.

I recreated the table and imported the data. Table worked fine, until it'd been used by the forms?

fLocations
Record Source: qLocation
(which pulls from tLocation, with lots of left joins to other tables)

[COLOR=Green]'------------------------------------------------------------
' cmdFilter_Click
'------------------------------------------------------------
[/COLOR][B]Private Sub cmdFilter_Click([/B])

Dim strFilterStart As String
Dim strFilterEnd As String

Dim strFilterBuilding As String
Dim strFilterRoom As String
Dim strFilterUsage As String
Dim strFitlerFaculty As String
Dim strFilterSchool As String
Dim strFilterSubject As String
Dim strFilterSpecialism As String
Dim strFilterPointOfContact As String
Dim strFilterAVCabinetPC As String
Dim strFilterAVWorkstation As String
Dim strFilterHistoricBuild As String
Dim strFilterOperatorAndWorkstation As String

strFilterStart = "SELECT * FROM qLocation WHERE ("
strFilterEnd = ");"

strFilterBuilding = "((qLocation.FIDBuilding) Like ""*"" & Forms![fNavigation].[NavigationSubform].Form.[cboFilterFIDBuilding] & ""*"")"
strFilterRoom = "((qLocation.Room) Like ""*"" & Forms![fNavigation].[NavigationSubform].Form.[txtFilterRoom] & ""*"")"
strFilterUsage = "((qLocation.FIDUsage) Like ""*"" & Forms![fNavigation].[NavigationSubform].Form.[cboFilterFIDUsage] & ""*"")"
strFitlerFaculty = "((qLocation.FIDFaculty) Like ""*"" & Forms![fNavigation].[NavigationSubform].Form.[cboFilterFIDFaculty] & ""*"")"
strFilterSchool = "((qLocation.FIDSchool) Like ""*"" & Forms![fNavigation].[NavigationSubform].Form.[cboFilterFIDSchool] & ""*"")"
strFilterSubject = "((qLocation.FIDSubject) Like ""*"" & Forms![fNavigation].[NavigationSubform].Form.[cboFilterFIDSubject] & ""*"")"
strFilterSpecialism = "((qLocation.FIDSpecialism) Like ""*"" & Forms![fNavigation].[NavigationSubform].Form.[cboFilterFIDSpecialism] & ""*"")"
strFilterPointOfContact = "((qLocation.PointOfContact) Like ""*"" & Forms![fNavigation].[NavigationSubform].Form.[cboFilterPointOfContact] & ""*"")"
strFilterAVCabinetPC = "((qLocation.AVCabinetPC) Like ""*"" & Forms![fNavigation].[NavigationSubform].Form.[cboFilterAVCabinetPC] & ""*"")"
strFilterAVWorkstation = "((qLocation.AVWorksation) Like ""*"" & Forms![fNavigation].[NavigationSubform].Form.[CheckAVFilter]) "
strFilterHistoricBuild = "((qLocation.FIDHistoricBuild) Like ""*"" & Forms![fNavigation].[NavigationSubform].Form.[cboFilterFIDHistoricBuild] & ""*"")"
strFilterOperatorAndWorkstation = "((qLocation.Workstation) " & Forms![fNavigation].[NavigationSubform].Form.[cboFilterWorkstationOperator] & Forms![fNavigation].[NavigationSubform].Form.[txtFilterWorkstation] & ")"

If IsNull(cboFilterWorkstationOperator) And IsNull(txtFilterWorkstation) Then

    Me.RecordSource = strFilterStart & strFilterBuilding & _
    " AND " & strFilterRoom & _
    " AND " & strFilterUsage & _
    " AND " & strFitlerFaculty & _
    " AND " & strFilterSchool & _
    " AND " & strFilterSubject & _
    " AND " & strFilterSpecialism & _
    " AND " & strFilterPointOfContact & _
    " AND " & strFilterAVCabinetPC & _
    " AND " & strFilterAVWorkstation & _
    " AND " & strFilterHistoricBuild & _
    strFilterEnd
 
 ElseIf Not IsNull(cboFilterWorkstationOperator) And Not IsNull(txtFilterWorkstation) Then
 
     Me.RecordSource = strFilterStart & strFilterBuilding & _
    " AND " & strFilterRoom & _
    " AND " & strFilterUsage & _
    " AND " & strFitlerFaculty & _
    " AND " & strFilterSchool & _
    " AND " & strFilterSubject & _
    " AND " & strFilterSpecialism & _
    " AND " & strFilterPointOfContact & _
    " AND " & strFilterAVCabinetPC & _
    " AND " & strFilterAVWorkstation & _
    " AND " & strFilterHistoricBuild & _
    " AND " & strFilterOperatorAndWorkstation & _
    strFilterEnd  'added workstation operator and number filter
 
 ElseIf IsNull(cboFilterWorkstationOperator) And Not IsNull(txtFilterWorkstation) Then
 
      Me.RecordSource = strFilterStart & strFilterBuilding & _
    " AND " & strFilterRoom & _
    " AND " & strFilterUsage & _
    " AND " & strFitlerFaculty & _
    " AND " & strFilterSchool & _
    " AND " & strFilterSubject & _
    " AND " & strFilterSpecialism & _
    " AND " & strFilterPointOfContact & _
    " AND " & strFilterAVCabinetPC & _
    " AND " & strFilterAVWorkstation & _
    " AND " & strFilterHistoricBuild & _
    " AND ((qLocation.Workstation)=" & Forms![fNavigation].[NavigationSubform].Form.[txtFilterWorkstation] & _
    strFilterEnd  'If operator not selected but there is number in the txtFilterWorkstation search as if "=" had been selected.
    
ElseIf Not IsNull(cboFilterWorkstationOperator) And IsNull(txtFilterWorkstation) Then
    
    Me.RecordSource = strFilterStart & strFilterBuilding & _
    " AND " & strFilterRoom & _
    " AND " & strFilterUsage & _
    " AND " & strFitlerFaculty & _
    " AND " & strFilterSchool & _
    " AND " & strFilterSubject & _
    " AND " & strFilterSpecialism & _
    " AND " & strFilterPointOfContact & _
    " AND " & strFilterAVCabinetPC & _
    " AND " & strFilterAVWorkstation & _
    " AND " & strFilterHistoricBuild & _
    strFilterEnd 'same as if both cboFilterWorkstationOperator and txtFilterWorkstation are null (i.e. ignore)
    
End If
 
[B]End Sub[/B]

[COLOR=Green]'------------------------------------------------------------
' Nulls all the filters for a new filter
'------------------------------------------------------------[/COLOR]
[B]Private Sub ClearFilters()[/B]

Me.cboFilterFIDBuilding = Null
Me.txtFilterRoom = Null
Me.cboFilterFIDUsage = Null
Me.cboFilterFIDFaculty = Null
Me.cboFilterFIDSchool = Null
Me.cboFilterFIDSubject = Null
Me.cboFilterFIDSpecialism = Null
Me.cboFilterPointOfContact = Null
Me.cboFilterWorkstationOperator = Null
Me.txtFilterWorkstation = Null
Me.cboFilterAVCabinetPC = Null
Me.CheckAVFilter = Null
Me.cboFilterFIDHistoricBuild = Null

[B]End Sub[/B]

[COLOR=Green]'------------------------------------------------------------
' cmdRefresh_Click - CLEARS FILTERS AND REFRESHES
'(do I need RecordSource = AND the DoCmd.RunCommand acCmdRefresh????
'------------------------------------------------------------[/COLOR]
[B]Private Sub cmdRefresh_Click()[/B]

On Error GoTo cmdRefresh_Click_Err

ClearFilters
Me.RecordSource = "SELECT * FROM [qLocation] " 'display all records from qLocation

DoCmd.RunCommand acCmdRefresh

cmdRefresh_Click_Exit:
    Exit Sub
cmdRefresh_Click_Err:
    MsgBox Error$
    Resume cmdRefresh_Click_Exit

[B]End Sub[/B]

[COLOR=Green]'------------------------------------------------------------
' cboFilters
'------------------------------------------------------------[/COLOR]
[B]Private Sub cboFilterFIDFaculty_AfterUpdate()[/B]

    Dim sSchoolSource As String
    sSchoolSource = "SELECT [tSchool].[IDSchool], [tSchool].[SchoolAbb], [tSchool].[SchoolName]  " & _
                        "FROM tSchool " & _
                        "WHERE [FIDFaculty] = " & Me.cboFilterFIDFaculty.Value
                                               
    Me.cboFilterFIDSchool.RowSource = sSchoolSource
    Me.cboFilterFIDSchool.Requery
    EnableControls

[B]End Sub[/B]

[B]Private Sub cboFilterFIDSchool_AfterUpdate()[/B]
    
    Dim sSubjectSource As String

    sSubjectSource = "SELECT  [tSubject].[IDSubject], [tSubject].[SubjectName] " & _
                        "FROM tSubject " & _
                        "WHERE [FIDSchool] = " & Me.cboFilterFIDSchool.Value
    Me.cboFilterFIDSubject.RowSource = sSubjectSource
    Me.cboFilterFIDSubject.Requery
    EnableControls

[B]End Sub

Private Sub cboFilterFIDSubject_AfterUpdate()[/B]
    
    Dim sSpecialismSource As String
    Dim sPoCSource As String
    
    sSpecialismSource = "SELECT [tSpecialism].[IDSpecialism], [tSpecialism].[SpecialismName] " & _
                        "FROM tSpecialism " & _
                        "WHERE [FIDSubject] = " & Me.cboFilterFIDSubject.Value
    Me.cboFilterFIDSpecialism.RowSource = sSpecialismSource
    Me.cboFilterFIDSpecialism.Requery
    
    sPoCSource = "SELECT [tStaff].[IDStaff] " & _
                        "FROM tStaff " & _
                        "WHERE [FIDSubject] = " & Me.cboFilterFIDSubject.Value
    Me.cboFilterPointOfContact.RowSource = sPoCSource
    Me.cboFilterPointOfContact.Requery
    
    EnableControls
    
[B]End Sub[/B]
[B]
Private Sub EnableControls()[/B]

If IsNull(Me.cboFilterFIDFaculty) Then
    Me.cboFilterFIDSchool = Null
End If

If IsNull(Me.cboFilterFIDSchool) Then
    Me.cboFilterFIDSubject = Null
End If

If IsNull(Me.cboFilterFIDSubject) Then
    Me.cboFilterFIDSpecialism = Null
End If

Me.cboFilterFIDSchool.Enabled = (Not IsNull(Me.cboFilterFIDFaculty)) 'IF cboFaculty is NOT NULL enable cboFilterFIDSchool
Me.cboFilterFIDSubject.Enabled = (Not IsNull(Me.cboFilterFIDSchool)) 'IF cboSchool is NOT NULL enable cboFilterFIDSubject
Me.cboFilterFIDSpecialism.Enabled = (Not IsNull(Me.cboFilterFIDSubject)) 'IF cboSubject is NOT NULL enable cboFilterFIDSpecialism
Me.cboFilterPointOfContact.Enabled = (Not IsNull(Me.cboFilterFIDSubject)) 'IF cboSubject is NOT NULL enable cboFilterPointOfContact
[B]End Sub[/B]

fLocationsSubform

Record Source: tLocation
Link Master Field: IDLocation
Link Child Field: IDLocation.
Fields: Various types. FIDs are converted to cbo's (bound to the FIDs, but displaying text)

[COLOR=Green]'------------------------------------------------------------
' New blank record add.... alternative to the Add New button wizard....? NOT WORKING
'
' Buttons available via Embedded Macros:
' cmdNew, cmdSave, cmdUndo, cndDelete - all via Button wizard. All but cmdNew working fine - cmdNew NOT WORKING, incorrect PK assignment.
'
'------------------------------------------------------------[/COLOR]
[B]Private Sub cmdNewV2_Click()[/B]

Dim db As DAO.Database
Dim rs As DAO.Recordset

    Set db = CurrentDb
    Set rs = db.OpenRecordset("tLocation", dbOpenDynaset)
    
With rs
.AddNew
!FIDBuilding = cboFIDBuilding
!Room = txtRoom
!FIDUsage = cboFIDUsage
'etc
.Update
End With
Me.AllowAdditions = False
rs.Close
Set rs = Nothing

[B]End Sub[/B]

[COLOR=Green]'------------------------------------------------------------
' cascading combo for single form data entry
'------------------------------------------------------------[/COLOR]
[B]Private Sub cboFIDFaculty_AfterUpdate()[/B]

    Dim sSchoolSource As String
    sSchoolSource = "SELECT [tSchool].[IDSchool], [tSchool].[SchoolAbb], [tSchool].[SchoolName]  " & _
                        "FROM tSchool " & _
                        "WHERE [FIDFaculty] = " & Me.cboFIDFaculty.Value
                                               
    Me.cboFIDSchool.RowSource = sSchoolSource
    Me.cboFIDSchool.Requery
    EnableControls

[B]End Sub

Private Sub cboFIDSchool_AfterUpdate()[/B]
    
    Dim sSubjectSource As String

    sSubjectSource = "SELECT  [tSubject].[IDSubject], [tSubject].[SubjectName] " & _
                        "FROM tSubject " & _
                        "WHERE [FIDSchool] = " & Me.cboFIDSchool.Value
    Me.cboFIDSubject.RowSource = sSubjectSource
    Me.cboFIDSubject.Requery
    EnableControls

[B]End Sub

Private Sub cboFIDSubject_AfterUpdate()[/B]
    
    Dim sSpecialismSource As String
    Dim sPoCSource As String
    
    sSpecialismSource = "SELECT [tSpecialism].[IDSpecialism], [tSpecialism].[SpecialismName] " & _
                        "FROM tSpecialism " & _
                        "WHERE [FIDSubject] = " & Me.cboFIDSubject.Value
    Me.cboFIDSpecialism.RowSource = sSpecialismSource
    Me.cboFIDSpecialism.Requery
    
    sPoCSource = "SELECT [tStaff].[IDStaff] " & _
                        "FROM tStaff " & _
                        "WHERE [FIDSubject] = " & Me.cboFIDSubject.Value
    Me.cboPointOfContact.RowSource = sPoCSource
    Me.cboPointOfContact.Requery
    
    EnableControls
    
[B]End Sub

Private Sub EnableControls()[/B]

If IsNull(Me.cboFIDFaculty) Then
    Me.cboFIDSchool = Null
End If

If IsNull(Me.cboFIDSchool) Then
    Me.cboFIDSubject = Null
End If

If IsNull(Me.cboFIDSubject) Then
    Me.cboFIDSpecialism = Null
End If

Me.cboFIDSchool.Enabled = (Not IsNull(Me.cboFIDFaculty)) 'IF cboFaculty is NOT NULL enable cboFIDSchool
Me.cboFIDSubject.Enabled = (Not IsNull(Me.cboFIDSchool)) 'IF cboSchool is NOT NULL enable cboFIDSubject
Me.cboFIDSpecialism.Enabled = (Not IsNull(Me.cboFIDSubject)) 'IF cboSubject is NOT NULL enable cboFIDSpecialism
Me.cboPointOfContact.Enabled = (Not IsNull(Me.cboFIDSubject)) 'IF cboSubject is NOT NULL enable cboPointOfContact
[B]End Sub[/B]

Has anyone got any ideas? Otherwise, I'm faced with scrapping this form and starting over and merging a load of normalised data into one table to get a continuous form that is editable. I haven't got time for that if I want to sleep/eat for the next few weeks.

My previous attempt at this form had no subform, just a continous form sourced from tLocation, but the cascading cbo boxes for the FID fields caused blanking issues and I'd eventaully found out that having cascading cboboxes like in a continuous form did that, and not to do it, so this was a suggestion. So I've tried it, confident I was on a good path and now this

THIS form is SO close to working, except for the new record function. Unfortunately an important one

Please, please, does anyone have any advice as to why this is happening and what to do about it?

 

[sneuberg]

it gives me the ID of the currently selected record in the continuous form rather than a new one (yes IDLocation is AutoNumber).

I'm probably just stating the obvious but it seem that somehow you need to force the continuous forms to a new record. It's probably picking up the Id from the currently selected record through the link fields. Just to confirm that you could try set the link fields to null before the add.

 

[NBRJ]

Hi Sneuberg, thank you for the quick response. I've tried de-linking the forms already and it didn't work. I'll try to do some more testing now and report back, see if I can get any more useful info to help pinpoint the problem.

I've also run the compact and repair to no joy.

 

[Minty]

I think Sneuberg is on the right track, you can't normally force a new Main record to be created from a linked child record. It wouldn't know which parent record to associate it with?

Edit : It might be worth posting up your database to have a look at to see if I'm understanding your issue correctly?

 

[NBRJ]

I think Sneuberg is on the right track, you can't normally force a new Main record to be created from a linked child record. It wouldn't know which parent record to associate it with?

That sounds like bad news

I've attached a stripped back, RL data removed.

The forms are now fLocationV2 and fLocationV2Subform (rebuilt today to make sure I hadn't done something stupid - entirely possible ).

EDIT: the continuous form fLocationV2 should have the fields that would ordinarily have CBOs on them locked - I forgot! Basically, they have to edit by the subform.

Also, I haven't yet added in the requery of the fLocationV2 mainform to the SAVE and DELETE buttons on the subform. Haven't figured out how to do that in an embedded marco, rather than VBA. The Clear Filter button will do the refresh for now.

 

[NBRJ]

It's like submitting an assignment - arrrghhh!

EDIT: One of the cboboxes is broken in the subform, it needs to be changed to:

Me.cboFIDSpecialism.RowSource = "SELECT tSpecialism.[IDSpecialism], tSpecialism.[SpecialismName] " & _
                           "FROM [B]tSpecialism [/B]" & _
                           "WHERE [FIDSubject] = " & Nz(Me.cboFIDSubject)

More haste, less speed...

 

[Minty]

Okay - I see where you are at now. The Sub form is a more detailed view of the continuous form data.
There are a number of ways to deal with this, the most simple would be to simply open the sub form as a normal popup modal form in add record mode, then on closing it refresh your underlying records.
You could also (And I've never tried this so it might not work) get cute and have the add new button remove the child parent links between the sub form then go to a new record. You would need to have some form of Save button to reset the links and refresh the main form.

 

[NBRJ]

Getting an error when entering a new item. When I select a building I get: You can't assign a value to this object.

But then I click OK, and I can...?

 

[sneuberg]

I noticed that if you select the last record in the continuous forms you can add a record without any problems.

 

[NBRJ]

Hi Minty, thanks for the prompt response again.

It's because of the need to have cascading cbo boxes that a subform is needed. There will be MANY facs > schools > subjects > specialisms when the data goes in, so having them as standalone cbos would be horrible. But I found when I had a continuous form with combo boxes in, I had massive blanking issues and weirdness going on within the continuous form and then found out that continuous form + cascading cbos = badness. I also had text boxes referencing the cbo's and the GotFocus went to the cbo behind it. Otherwise all those arrows took up too much space to be readable. Took me ages, too.... and it didn't work. Live and learn.

So this idea was attempt 2. I like this idea. It looks much neater, if the problems with new entries can be solved. Having said that, I haven't got this to break yet, except for that message: You can't assign a value to this object.

Has anyone else managed to break it?

 

[Minty]

There is another way - on your add record button you could insert a new blank record in code to your table with a room number as "New" , and then refresh and select the last record (Highest ID) in the main form.

 

[NBRJ]

I don't know if I need to worry now. I can't break the new record ID assignment, it's always using a new one. In that regard it's working without problem for me. Unless you got it to fail at that?

Still got the You can't assign a value to this object message. Which is false, as I can once I click ok. Anyway to supress just this message? Or what is causing it?

 

[Minty]

I would tidy it up probably using the method I described - other users will get confused by the error message , and the fact that they can't see the new record appearing in the main list.
I can knock up some code if you wanted to see how to go about it.

 

[NBRJ]

Hi Minty, that would be great if you could give me a pointer because I'm not sure how that would work (think I'm confused about what you're suggesting!).

I had assumed that all I needed was to add a requery to the save (and delete) button (embedded macro) to refresh the fLocationV2 form. I've still not got this version of the form to break with the "The changes you requested to the table were not successful because they would create duplicate values..." message, so the addition of new records seems to be working except for the You can't assign a value to this object message for FIDBuilding. Is that because tBuilding is the parent of tLocation and FIDBuilding MUST have data?

Although if what you're suggesting fixes that other message, that would be super. I need to understand what you're suggesting though. I need to get an idea of the logic.

 

[Minty]

OKay - I'll post up you db with a sample shortly.

 

[Minty]

See the attached for a better way of adding a new record.
There is some code added to both the Sub form New button and the Parent form to go to the added record.

You might need to reset/clear your form filters before firing this as it relies on finding the new record to work correctly, but I think you'll get the drift.

 

[NBRJ]

Minty, you are a star. That you, and others, do this, is a wonderful thing. Expanding people's knowledge and understanding should be valued more highly. Thank you.