make fields selectively updatable

E

exaccess

Registered User.
Local time
Today, 11:11
Joined
Apr 21, 2013
Messages
287
Hello Experts,
I have a form consisting of a list box and some fields belonging to a single record. When the user highlights a row in the list box then the fields show the details of that record. Now the whole process is Read only. But I wish to put a toggle switch on the form so that the detail fields of the record become updateable. I have tried the following constructs but I could not make those fields updateable.
Code: 
Me.AllowEdits = True
Me.DataEntry = True
Me.fld1.Enabled = True
Me.fld2.Locked = True
Help is highly appreciated.
 
Here are a few thoughts:
First of all, is the query that drives the form updatable? Then, DataEntry should be false, otherwise the form always opens to a new record, and will filter out--and not show--existing data. Finally, and obviously, setting . . .
Code: 
fld2.Locked = True
. . . locks the field, and makes it impossible to edit.
 
yes, it's probably confusing

allowedits is a blunt instrument, as it stops you changing anything, including unbound combo boxes, that you probably do not want to lock

dataentry is used to allow an inputter to enter new records, but not see existing ones. useful in some cases, but probably not for what you want



enabled and locked work together

enabled true, locked false - fully editable

enabled true, locked true - control can be entered but not changed - you need to enter a control to be able to sort or search on it, so this is needed sometimes

enabled false, locked true - you cannot interact with this at all

enabled false, locked false - special setting that provides a "greyed" control


you have to set locked and enabled properties for individual controls as you require them.
 
MarkK said:
Here are a few thoughts:
First of all, is the query that drives the form updatable? Then, DataEntry should be false, otherwise the form always opens to a new record, and will filter out--and not show--existing data. Finally, and obviously, setting . . .
Code: 
fld2.Locked = True
. . . locks the field, and makes it impossible to edit.
Click to expand...
fld2 should be fld1 my mistake sorry for that.
The query that drives this form is an SQL select query. I converted it to an SQL UPDATE query but this time it is rejected as the recordsource.
I tried to run it with the DoCmd.RunSQL but this time it asks the value of every field instead of getting it from the screen.
 
yes, it's probably confusing

allowedits is a blunt instrument, as it stops you changing anything, including unbound combo boxes, that you probably do not want to lock

dataentry is used to allow an inputter to enter new records, but not see existing ones. useful in some cases, but probably not for what you want
Click to expand...

I have deleted alowedits and dataentry.


enabled and locked work together

enabled true, locked false - fully editable
Click to expand...

I have done that.

you have to set locked and enabled properties for individual controls as you require them.[
Click to expand...

I did that.

It still does not work.
 
exaccess said:
I have deleted alowedits and dataentry.
I have done that.
I did that.
It still does not work.
Click to expand...

what mark meant is this:

just open the query in the navigation pane. ignore the form for the moment

now, can you change stuff in the query - or does it say in the status line "this recordset is not updateable" (or similar)

for various reasons, some queries do not allow edits you need to be sure that is not the problem.
 
gemma-the-husky said:
what mark meant is this:

just open the query in the navigation pane. ignore the form for the moment

now, can you change stuff in the query - or does it say in the status line "this recordset is not updateable" (or similar)

for various reasons, some queries do not allow edits you need to be sure that is not the problem.
Click to expand...

Done that. It says recordset is not updatable.
 
From post #2 . . .
First of all, is the query that drives the form updatable?
Click to expand...
Some queries show data from multiple records . . .
Code: 
SELECT Field1, Sum(Field2) As SumField2
FROM Table
GROUP BY Field1
. . . and are not updatable, since there is no single source record to update. Each record in the query is an aggregate of all the records selected from the table. Maybe you should show your query text.
 
MarkK said:
From post #2 . . .
Some queries show data from multiple records . . .
Code: 
SELECT Field1, Sum(Field2) As SumField2
FROM Table
GROUP BY Field1
. . . and are not updatable, since there is no single source record to update. Each record in the query is an aggregate of all the records selected from the table. Maybe you should show your query text.
Click to expand...
Thıs is the query which is the rowsource of the listbox and the single detail record on the screen:
Code: 
Sub Searcher(sterm)
    Dim ForName As String, Cap As String
    Dim QryName As String
    Dim qdf As DAO.QueryDef
    ForName = "ListNumbersFm"
    QryName = "SelQy"
    Dim RowSourceQy As String, RecSourceQy As String, SQLStatement As String
    
        Select Case sterm
        Case "Word"
            sterm = InputBox("Please give word to search for", "Input requested", "Cel")
            If sterm = "" Then GoTo Input_Error
            RowSourceQy = "SELECT TelephoneAAA.*  FROM TelephoneAAA " & _
                "WHERE (TelephoneAAA.SOYADI Like '*" & sterm & "*')" & _
                "or (TelephoneAAA.ADI Like '*" & sterm & "*')" & _
                "or (TelephoneAAA.ADRES Like '*" & sterm & "*')" & _
                "ORDER BY TelephoneAAA.SOYADI, TelephoneAAA.ADI, TelephoneAAA.TEL;"
            SQLStatement = RowSourceQy
            Cap = "Contacts whose records contain " & sterm
        Case "Word2"
            sterm = InputBox("Please give word to search for", "Input requested", "Cel")
            If sterm = "" Then GoTo Input_Error
            RowSourceQy = "SELECT TelephoneAAA.*  FROM TelephoneAAA " & _
                "WHERE (TelephoneAAA.SOYADI Like '*" & sterm & "*')" & _
                "or (TelephoneAAA.ADI Like '*" & sterm & "*')" & _
                "or (TelephoneAAA.ADRES Like '*" & sterm & "*')" & _
                "ORDER BY TelephoneAAA.ADI, TelephoneAAA.SOYADI, TelephoneAAA.TEL;"
            SQLStatement = RowSourceQy
            Cap = "Contacts whose records contain " & sterm
        Case "All"
            RowSourceQy = "SELECT TelephoneAAA.*  FROM TelephoneAAA " & _
            "ORDER BY TelephoneAAA.SOYADI, TelephoneAAA.ADI, TelephoneAAA.TEL;"
            SQLStatement = RowSourceQy
            Cap = "All Contacts"
        Case "Number"
            sterm = InputBox("Please give number to search for", "Input requested")
            If sterm = "" Then GoTo Input_Error
            RowSourceQy = "SELECT TelephoneAAA.*  FROM TelephoneAAA " & _
                "WHERE ((TelephoneAAA.TEL) Like '*" & sterm & "*')" & _
                "ORDER BY TelephoneAAA.SOYADI, TelephoneAAA.ADI, TelephoneAAA.TEL;"
            SQLStatement = RowSourceQy
            Cap = "Contacts whose telephones contain " & sterm
        Case "Soyadi"
            sterm = InputBox("Please give Character to search for", "Input requested")
            If sterm = "" Then GoTo Input_Error
            RowSourceQy = "SELECT TelephoneAAA.*  FROM TelephoneAAA " & _
                "WHERE TelephoneAAA.SOYADI Like '" & sterm & "*' " & _
                "ORDER BY TelephoneAAA.SOYADI, TelephoneAAA.ADI, TelephoneAAA.TEL;"
            SQLStatement = RowSourceQy
            Cap = "Contacts whose lastnames start with " & sterm
        
    End Select
        
    If QueryExists(QryName) = True Then CurrentDb.QueryDefs.Delete (QryName)
    Set qdf = CurrentDb.CreateQueryDef(QryName, SQLStatement)
    DoCmd.OpenForm ForName, acNormal, , , acFormPropertySettings, acHidden
    Forms(ForName).LboPeople.RowSource = RowSourceQy
    Dim J1 As Integer, J2 As Integer
    
    Debug.Print "Forms(ForName).LboPeople.ListIndex="; Forms(ForName).LboPeople.ListIndex
        Debug.Print "Forms(ForName).LboPeople.Value="; Forms(ForName).LboPeople.Value
    J1 = Forms(ForName).LboPeople.ItemData(1)
    RecSourceQy = "SELECT distinct [TelefID], [ADI],[SOYADI],[TEL],[ADRES], [DateOfUpdate] FROM [TelephoneAAA] " & _
        " WHERE " & _
        "TelephoneAAA.TelefID = " & J1 & ";"
    Forms(ForName).RecordSource = RecSourceQy
    Forms(ForName).PeopleLabel = Cap
    Forms(ForName).nnum = DCount("*", QryName)
    Forms(ForName).LboPeople.SetFocus
    DoCmd.OpenForm ForName, acNormal, , , acFormEdit, acDialog
    Debug.Print "Forms(ForName).LboPeople.ItemData(1)="; Forms(ForName).LboPeople.ItemData(1)
    GoTo Exit_Sub
Input_Error:
    MsgBox "Input error", vbOKOnly, Mtitle
Exit_Sub:
End Sub
This the code describing the record source and update queries. It does not work.
Code: 
Private Sub EditModeMode()
    Dim QryName As String, ForName As String, RecSourceQy As String
    ForName = "ListNumbersFm"
    Dim J1 As Integer, J2 As Integer
    Debug.Print "Forms(ForName).LboPeople.ListIndex="; Forms(ForName).LboPeople.ListIndex
    Debug.Print "Forms(ForName).LboPeople.Value="; Forms(ForName).LboPeople.Value
    J1 = Forms(ForName).LboPeople.Value
    RecSourceQy = "SELECT distinct [TelefID], [ADI],[SOYADI],[TEL],[ADRES], [DateOfUpdate] FROM [TelephoneAAA] " & _
        " WHERE " & _
        "TelephoneAAA.TelefID = " & J1 & ";"
    Forms(ForName).RecordSource = RecSourceQy
    Me.TglEdit.Caption = "Edit Mode"
    Me.ADI.Enabled = True
    Me.SOYADI.Enabled = True
    Me.TEL.Enabled = True
    Me.ADRES.Enabled = True
    Me.DATE.Enabled = True
    Me.ADI.Locked = False
    Me.SOYADI.Locked = False
    Me.TEL.Locked = False
    Me.ADRES.Locked = False
    Me.DATE.Locked = False
   
    Debug.Print "Forms(ForName).LboPeople.ListIndex="; Forms(ForName).LboPeople.ListIndex
    Debug.Print "Forms(ForName).LboPeople.Value="; Forms(ForName).LboPeople.Value
    J1 = Forms(ForName).LboPeople.Value
    Debug.Print "Forms(ForName).[ADI]="; Forms(ForName).[ADI]
    SQLStatement = "UPDATE [TelephoneAAA] " & _
        " SET [ADI] = " & "'" & Forms(ForName).[ADI] & "'" & " ,[SOYADI] = " & "'" & Forms(ForName).[SOYADI] & "'" & _
        " ,[TEL] = " & "'" & Forms(ForName).[TEL] & "'" & _
        " ,[ADRES] = " & "'" & Forms(ForName).[ADRES] & "'" & " ,[DateOfUpdate] = " & "'" & DATE & "'" & _
        " WHERE " & _
        " [TelefID] = " & J1 & " ;"
    DoCmd.RunSQL SQLStatement
   
End Sub
 
This query is not updatable because it uses the distinct keyword.
Code: 
    RecSourceQy = "SELECT [COLOR="DarkRed"]distinct[/COLOR] [TelefID], [ADI],[SOYADI],[TEL],[ADRES], [DateOfUpdate] FROM [TelephoneAAA] " & _
        " WHERE " & _
        "TelephoneAAA.TelefID = " & J1 & ";"
Remove the word distinct and retry.

Also, in general, "it does not work" is not enough info. If you post here, we already know it doesn't work. Describe symptoms.

Cheers,
 
MarkK said:
This query is not updatable because it uses the distinct keyword.
Code: 
    RecSourceQy = "SELECT [COLOR="DarkRed"]distinct[/COLOR] [TelefID], [ADI],[SOYADI],[TEL],[ADRES], [DateOfUpdate] FROM [TelephoneAAA] " & _
        " WHERE " & _
        "TelephoneAAA.TelefID = " & J1 & ";"
Remove the word distinct and retry.

Also, in general, "it does not work" is not enough info. If you post here, we already know it doesn't work. Describe symptoms.

Cheers,
Click to expand...

Hello Markk,
I did what you said but this time although the listbox is populated the detail record part becomes empty.
 
OK, so we've solved the updatability problem, which is what this whole thread was about, is that correct?
 
MarkK said:
OK, so we've solved the updatability problem, which is what this whole thread was about, is that correct?
Click to expand...

Sorry I was quite busy with other stuff. I managed to sort out both problems. Thanks for the help.
 
You must log in or register to reply here.

Similar threads

I
Late Control binding to make Forms Field independant.
2 3
Replies
58
Views
1,897
isladogs
isladogs
E
Solved Select Last Record
Replies
10
Views
1,195
Emma35
E
S
Solved Multiple listboxes on main form
Replies
6
Views
742
soupy8728
S
P
Form Screen Flicker
Replies
8
Views
827
phillip
P
W
How to Populate a Control in Code and Make it Behave As Though Populated in the GUI
Replies
10
Views
505
arnelgp
arnelgp

Users who are viewing this thread

Total: 1 (members: 0, guests: 1)
Back
Top Bottom