Control with values from a query OR a table (1 Viewer)

[Notiophilus]

I have an option group which should display a value from a table if txtWords is null, but the result of a query if txtWords is not null. But though it shows the right results when I load the form, updating txtWords gives me all sorts of errors. Can someone help me work through this?

txtWords is in the main form, while the option group optLengthCat is in a subform (but inline).

Form_OnCurrent() event:

'Enable or disable the option group
    If Len(Me.txtWords.Value & vbNullString) = 0 Then
    Me.fsubLengthCat.Form!optLengthCat.Enabled = True
    Else
    Me.fsubLengthCat.Form!optLengthCat.Enabled = False
    End If

'Subform length option box
    
    Dim db As DAO.Database
    Dim qd As DAO.QueryDef
    Dim rs As DAO.Recordset

    'Set myDB
    Set db = CurrentDb()

    'Check if Data Exists
    Set qd = db.QueryDefs("qryLength")
    Set rs = qd.OpenRecordset()
    
    If rs.RecordCount <> 0 Then
        If Not Len(Me.txtWords.Value & vbNullString) = 0 Then
            Dim LLength As Long
            LLength = DLookup("[LengthID]", "qryLength", "[StoryID] = " & Forms![frmStories].[StoryID])
            Me.fsubLengthCat.Form!optLengthCat.Value = LLength
        End If
    End If

    Set rs = Nothing
    Set qd = Nothing
    Set db = Nothing

When I put this into txtWords_AfterUpdate() it starts throwing up errors. Specifically, when I delete a subform record using SQL I get 'record was already deleted by another user'; I think I might have fixed the problem with Me.Requery, but it feels really inefficient. :banghead:

Should I be using an unbound control? If yes, how do I make an unbound control that can edit or add new records?

 

[pbaldy]

In general you should either manipulate data via the bound form or SQL/recordset. When you do both you can get that conflict error. Using Requery or explicitly saving a record prior to using SQL can work around it, so it sounds like you've already found the fix.

 

[Notiophilus]

Trouble is, the option group won't update with the query results afterwards. I checked in debug mode, and the DLookup from the query gives 'Empty' when I mouse over.

Dim LLength As Variant
LLength = DLookup("[LengthID]", "qryLength", "[StoryID] = " & Forms![frmStories].[StoryID]) [COLOR=Red]= Empty[/COLOR]
Me.fsubLengthCat.Form!optLengthCat.Value = LLength

The only way to update is to move to a new record, then go back (so I'm just using OnCurrent again).

...Aaaand the deleted records are reborn with new values from the query.

 

[pbaldy]

Are you aware that in debug mode, you'd have to move past that line before the variable
would have a value? I assume so, but had to ask. I guess I don't follow what the query is used for and what errors you get. Can you post the db here, or a representative sample that exhibits the error?

 

[Notiophilus]

Are you aware that in debug mode, you'd have to move past that line before the variable
would have a value? I assume so, but had to ask.

I've gone over it in different cases - turns out the variable is taking on the value of the query before I changed the amount in txtWords.

Will upload a simplified version of my database.

 

[Notiophilus]

Sorry it took me so long to get back to this... I'm crap at closure. Ended up fixing it myself when I made the simplified database. There were two problems:

1. optLengthCat wouldn't update with txtWords unless I left the record, because the record needed for the query hadn't been written yet. Fixed by adding DoCmd.RunCommand acCmdSaveRecord on the txtWords_AfterUpdate event
2. the (calculated) value of optLengthCat obtained from txtWords was being added to tblStoryLength (supposed to hold user estimates only). Easy fix: optLengthCat needs to be unbound, because a bound control saves all changes to its value
   Values are entered in tblStoryLength.LengthID by UPDATE/INSERT INTO queries in the control's OnClick event (which excludes changes to txtWords) and deleted by a DELETE query if txtWords <> 0 after updating.

Thanks for your help!

 

[pbaldy]

Glad you got it sorted out.