Page 1 of 2 12 LastLast
Results 1 to 15 of 17
  1. #1
    roberto21 is offline Novice
    Windows 10 Access 2016
    Join Date
    Apr 2019
    Posts
    17

    Cannot edit form fields after filling form from table

    I have a simple form, with an unbound field and all other fields linked to a table. The user enters a value in the unbound field, and the form is fllled with the values taken from the table record to the enterd value. The purpose is to allow changes in one or more fields, and save the edited record. The form properties:

    dataentry yes
    allow edit yes
    allow addition no
    allow deletion no
    record lock edited records

    all fields are

    enabled yes
    locked no

    However, when the form is filled, all fields seem to be locked and cannot be edited. What am I doing wrong, or what am I missing?


    The linked table resides in the database under sqlexpress server, and the form is filled with the following code

    Code:
    Private Sub STessElett_AfterUpdate()
    Dim rst As DAO.Recordset, myquery As String
        Me.DataEntry = False
        'check if tessera elettronica exists
          myquery = "SELECT * FROM LibroSoci WHERE [Tessera Elettronica  n] = " & Me.STessElett
          
          Set rst = CurrentDb.OpenRecordset(myquery, dbOpenSnapshot)
          If rst.RecordCount = 0 Then
            MsgBox ("Numero tessera elettronica inesistente")
          Else
            Me.DataEntry = True
            DoCmd.OpenForm "FrmLibroSociModifica", acNormal, , WhereCondition:="[Tessera Elettronica  n]= " & STessElett
          End If
    End Sub

    Thank you.

  2. #2
    Join Date
    Jan 2017
    Location
    Swansea,South Wales,UK
    Posts
    4,859
    Data entry should be No?
    That only allows you to add records? Though the link below states otherwise?
    Most misnamed property in Access

    https://support.microsoft.com/en-us/...0-4aa4acd8fe87
    Please use # icon on toolbar when posting code snippets.
    Cross Posting: https://www.excelguru.ca/content.php?184
    Debugging Access: https://www.youtube.com/results?sear...bug+access+vba

  3. #3
    roberto21 is offline Novice
    Windows 10 Access 2016
    Join Date
    Apr 2019
    Posts
    17
    Thanks for the attention. You are probably right about DataEntry, but...
    I must be missing something else. Changed Dataentry to NO, and removed line "me.dataentry = true" from the code. Same behavior, fields cannot be edited.
    I also changed
    Code:
    Set rst = CurrentDb.OpenRecordset(myquery, dbOpenSnapshot)
    to
    Code:
    Set rst = CurrentDb.OpenRecordset(myquery, dbOpenDynaset)
    after reading somewhere that Snapshot is read only by nature. But nothing has changed

  4. #4
    Join Date
    Jan 2017
    Location
    Swansea,South Wales,UK
    Posts
    4,859
    Is the query read only?
    Please use # icon on toolbar when posting code snippets.
    Cross Posting: https://www.excelguru.ca/content.php?184
    Debugging Access: https://www.youtube.com/results?sear...bug+access+vba

  5. #5
    roberto21 is offline Novice
    Windows 10 Access 2016
    Join Date
    Apr 2019
    Posts
    17
    Not that I know. It is a very simple query, you can see it in the code.

  6. #6
    Join Date
    Jan 2017
    Location
    Swansea,South Wales,UK
    Posts
    4,859
    You are playing aound with DataEntry property on one form yet using another. for the edit?
    What is that form FrmLibroSociModifica properties set to?

    I only have 2007, but if you upload enough for the problem, if I cannot open the DB, someone will be able to?

    Snapshot was definitely not the way to go?
    I am not sure why you are even using query? A DlookUp/DCount() would tell you if that record existed?, but that is not the cause of the issue, just an observation.
    Please use # icon on toolbar when posting code snippets.
    Cross Posting: https://www.excelguru.ca/content.php?184
    Debugging Access: https://www.youtube.com/results?sear...bug+access+vba

  7. #7
    Missinglinq's Avatar
    Missinglinq is offline VIP
    Windows 7 64bit Access 2007
    Join Date
    May 2012
    Location
    Richmond (Virginia, not North Yorkshire!)
    Posts
    3,016
    In addition to changing DataEntry to False/No...I'd also change

    record lock edited records

    to

    No Locks

    But more importantly...Do your Tables in SQL Server all have Primary Keys, or at least Unique Indexes, defined for them? Is the PK part of the RecordSource of the Form? If not they’ll be Read-Only using ODBC.

    Linq ;0)>
    The problem with making anything foolproof...is that fools are so darn ingenious!

    All posts/responses based on Access 2003/2007

  8. #8
    roberto21 is offline Novice
    Windows 10 Access 2016
    Join Date
    Apr 2019
    Posts
    17
    Soirry, I did not understand your remark. I have only one form FrmLibroSociModifica, bound to the table LibroSoci. The form is filled with the record requested by the user (if such record exists), so that the user should be able to modify something before writing the record back. The properties of the form are listed in my first post, except now DataEntry is set to NO.
    Of course, if I open the table itself, all fields can be changed.

  9. #9
    Join Date
    Jan 2017
    Location
    Swansea,South Wales,UK
    Posts
    4,859
    Quote Originally Posted by roberto21 View Post
    Soirry, I did not understand your remark. I have only one form FrmLibroSociModifica, bound to the table LibroSoci. The form is filled with the record requested by the user (if such record exists), so that the user should be able to modify something before writing the record back. The properties of the form are listed in my first post, except now DataEntry is set to NO.
    Of course, if I open the table itself, all fields can be changed.
    But you are opening another instance of the form are you not?
    If I was doing this I would just locate the record in the form I am already using?
    Plus I have no experience with SQLserver, express or otherwise with Access.
    Please use # icon on toolbar when posting code snippets.
    Cross Posting: https://www.excelguru.ca/content.php?184
    Debugging Access: https://www.youtube.com/results?sear...bug+access+vba

  10. #10
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    DataEntry setting has no effect if allow additions is no so that's not it. The query being simple doesn't really matter as there are many reasons why it might not be editable, and snapshot is definitely one of them. I don't see the point in applying a filter to the form opening when you're already setting the recordsource using the same criteria. Perhaps filtering the form is the cause. Can't say because I wouldn't purposely apply a filter to a form which should already be filtered by the same recordsource.

    I don't see what the issue is regarding the info at the link. Could you elaborate, Welshgasman?
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  11. #11
    Join Date
    Jan 2017
    Location
    Swansea,South Wales,UK
    Posts
    4,859
    Quote Originally Posted by Micron View Post
    DataEntry setting has no effect if allow additions is no so that's not it. The query being simple doesn't really matter as there are many reasons why it might not be editable, and snapshot is definitely one of them. I don't see the point in applying a filter to the form opening when you're already setting the recordsource using the same criteria. Perhaps filtering the form is the cause. Can't say because I wouldn't purposely apply a filter to a form which should already be filtered by the same recordsource.

    I don't see what the issue is regarding the info at the link. Could you elaborate, Welshgasman?
    I had seen from various posts in multiple forums, that DataEntry set to yes, only allows new records? I have never set that property to yes. I have advised others in the past who have done so, and when they corrected that property, their form worked.

    However I noticed on that link that it said that the property did not affect adding new records?
    However I left the link for information purposes only, just in case the o/p cared to actually look to see what that property does.?

    I dare say the o/p could test the query, just by editing the data in that query directly to see if it could be read only?
    I admit, I am puzzled as to why the o/p is opening the same form again for the same criteria.
    Please use # icon on toolbar when posting code snippets.
    Cross Posting: https://www.excelguru.ca/content.php?184
    Debugging Access: https://www.youtube.com/results?sear...bug+access+vba

  12. #12
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    Well I see that I made a boo boo in that the sql is a test for a value, not the recordsource for a form. DLookup is what I'd use rather than creating recordsets to find one value in one record. I don't see where the same form is being opened again. Am I missing something?

    Agree, query behind the form should be tested for editing as one can edit a filtered form, I see. Possible that the table itself is read only as well. From M$
    The Data Entry property doesn't determine whether records can be added; it only determines whether existing records are displayed.
    I take that to mean that Data Entry property only dictates whether or not existing records are displayed, and the answer would be No when that property setting is Yes, it opens the form to a new record only. However, it also does not dictate if records can be added - that is over-ridden by other factors such as whether or not the recordsource will allow record additions, or as the article states, whether or not the AllowAddtions property is set to No. So I don't agree with
    DataEntry set to yes, only allows new records?
    especially since the Yes setting is ignored if AllowAdditions is No. That makes sense - why would you allow a user to open to a new record if new records are not allowed by another property setting (one must ignore the other factors which can prevent record additions or edits when considering just how the form opens).

    I'd say that to open a form to a new record and not show any other records would require
    DataEntry = yes
    AllowAdditions = yes
    AllowEdits = yes
    AllowDeletions - don't know the requirements. Yes would allow user to delete the record just created, assuming it has been committed in the first place,
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  13. #13
    Join Date
    Jan 2017
    Location
    Swansea,South Wales,UK
    Posts
    4,859
    @Micron,
    The o/p stated they only had the one form, when I mentioned although they were setting data entry for the form,they were actually opening the form again with that criteria from the recordset?, so even if they set DataEntry to No forthe current form, that would not affect the new form being opened, even if it is the same form, surely? Not something I have ever done, or likely to contemplate, so not on firm ground here��
    Please use # icon on toolbar when posting code snippets.
    Cross Posting: https://www.excelguru.ca/content.php?184
    Debugging Access: https://www.youtube.com/results?sear...bug+access+vba

  14. #14
    Gicu's Avatar
    Gicu is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jul 2015
    Location
    Kelowna, BC, Canada
    Posts
    4,101
    What you're doing is not "populating the form", but finding the bound record that matches your entry (STessElett). The easiest way to do that (and more user-friendly as it minimizes the risk of typing mistakes) is to use an unbound combo.

    Here is a link showing you how to do that step by step:
    https://support.microsoft.com/en-us/...b-b0f71a90c329

    In the end you should end-up with something like this (the DataEntry should be set to No and AllowEdit=Yes):

    Code:
    Private Sub STessElett_AfterUpdate()
         ' Find the record that matches the control.
         Dim rs As Object
    
    
     Set rs = Me.Recordset.Clone
         rs.FindFirst "[STessElett] = " & Me.STessElett
         If Not rs.EOF Then Me.Bookmark = rs.Bookmark
    End Sub
    See https://docs.microsoft.com/en-us/off...-box-selection for more details.



    Cheers,
    Vlad
    Vlad Cucinschi
    MS Access Developer
    http://forestbyte.com/

  15. #15
    Join Date
    Jan 2017
    Location
    Swansea,South Wales,UK
    Posts
    4,859
    Quote Originally Posted by Micron View Post
    Well I see that I made a boo boo in that the sql is a test for a value, not the recordsource for a form. DLookup is what I'd use rather than creating recordsets to find one value in one record. I don't see where the same form is being opened again. Am I missing something?

    Agree, query behind the form should be tested for editing as one can edit a filtered form, I see. Possible that the table itself is read only as well. From M$
    I take that to mean that Data Entry property only dictates whether or not existing records are displayed, and the answer would be No when that property setting is Yes, it opens the form to a new record only. However, it also does not dictate if records can be added - that is over-ridden by other factors such as whether or not the recordsource will allow record additions, or as the article states, whether or not the AllowAddtions property is set to No. So I don't agree with especially since the Yes setting is ignored if AllowAdditions is No. That makes sense - why would you allow a user to open to a new record if new records are not allowed by another property setting (one must ignore the other factors which can prevent record additions or edits when considering just how the form opens).

    I'd say that to open a form to a new record and not show any other records would require
    DataEntry = yes
    AllowAdditions = yes
    AllowEdits = yes
    AllowDeletions - don't know the requirements. Yes would allow user to delete the record just created, assuming it has been committed in the first place,
    I read in another forum that the poster was surprised that the parameters supplied in opening the form overrode the form properties? My thoughts were, well that makes the form more flexible?
    Please use # icon on toolbar when posting code snippets.
    Cross Posting: https://www.excelguru.ca/content.php?184
    Debugging Access: https://www.youtube.com/results?sear...bug+access+vba

Page 1 of 2 12 LastLast
Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 3
    Last Post: 11-28-2015, 03:51 PM
  2. Replies: 6
    Last Post: 09-22-2013, 09:52 PM
  3. Auto filling a form from table
    By JoScSM in forum Forms
    Replies: 1
    Last Post: 11-02-2010, 08:09 AM
  4. Filling in a table via a form
    By janjan_376 in forum Forms
    Replies: 1
    Last Post: 07-06-2009, 01:57 AM
  5. Auto filling form fields
    By adamch29 in forum Forms
    Replies: 1
    Last Post: 07-25-2007, 06:22 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums