Page 1 of 2 12 LastLast
Results 1 to 15 of 17
  1. #1
    Join Date
    Jun 2004
    Location
    Seattle, WA
    Posts
    601

    Unanswered: How to best edit a record

    I was wondering how you guys handle record editing. I made a form with a subform, last week that pulled up data from my tables. The way the form knew which record to pull was a simple parameter query. After the form was launched, I saw the filter would be on. Last week, I could click the filter and edi any control except the search field.

    Now, I cannot edit anything other than the subform. I was wondering how I can make it so the other controls are editable. None of them are locked.
    Ryan
    My Blog

  2. #2
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926
    Did you perhaps change the recordset of the form to distinct? You cannot edit a form with a recordset based on distinct records.

    Has anything changed with your table structure?

    Your MainForm is normally based on 1 table and the subform is based on your other relational table which is linked to the mainform by the primary key field(s). If you've included the 2 tables in either forms, you may want to look at changing it so each form is based on 1 table and the MainForm to Subform link is established by looking at the properties of the subform while in the MainForm design mode and looking at the Link Child Fields and Link Master Fields values (see image below for the Subform properties).
    Attached Thumbnails Attached Thumbnails PropertyForSubForms.bmp  
    Last edited by pkstormy; 07-30-07 at 17:43.
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

  3. #3
    Join Date
    Jun 2004
    Location
    Seattle, WA
    Posts
    601
    Seeing that I don't know where I would check the recordset, I will say I didn't change it. The recordset property sats synaset.

    Nothing has changeddd in the table structure. The main and sub form are off of their own tables, the tables are in relation with another through a pk. My subform properties match the properties in your attachment.
    Ryan
    My Blog

  4. #4
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926
    Go into design mode of your main form. Show the properties and look at the Record Source (top item) - Make sure you're looking at the properties of the form and not of a field or other item. Click the ... on the right side of the Record Source line. If it says something to the effect of "Do you want to create a query based on the table" then it means you have the form based on a table verses a query. If it doesn't come up with this message, it will show you the query design. Run the query and see if you can edit any of the data. If you can't, then your problem is related to how this query is constructed for your main form (if you look at the properties of the query, check to see that Unique Values or Unique Records are both No). Try then closing the query and changing the Record Source to just the table for your main form by clicking the dropdown for Record Source and selecting the appropriate table. Make sense?
    Last edited by pkstormy; 07-30-07 at 18:33.
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

  5. #5
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926
    If the Record Source of the main form is based on a table and not a query (ie. instead of seeing the table name in the Record Source line, you see a "Select ....." which indicates it's a query), and you still can't edit any of the data, try removing what's in the Filter line in the line below the Record Source for the properties of the Main form. If you still can't edit any data, try looking at the other properties of the form to see if the Allow Edits might possibly be set to No or the Record Locks value is set to something other than No Locks.
    Last edited by pkstormy; 07-30-07 at 18:37.
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

  6. #6
    Join Date
    Jun 2004
    Location
    Seattle, WA
    Posts
    601
    Ok, I am able to edit everything once I make it based on the table not a query. Everything updates correctly. Now how will I be able to allow my users find a specific record? Yeah I just scrolled there and modified okne at random, now I can load a 100+ row table if I wanted to. That would be a pain to scroll through.
    Ryan
    My Blog

  7. #7
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Quote Originally Posted by rguy84
    Now how will I be able to allow my users find a specific record?
    Add a search function to your form?
    Simplest method:
    Code:
    DoCmd.GoToRecord , , acGoTo, Me.txtSearch.Value
    George
    Home | Blog

  8. #8
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926
    This is the funnest part (that I like designing). There are several different ways. Like georgev showed above is the simpliest way. Also, typically you have a button which opens a search form for you to select multiple criteria. There are some really great examples of search forms in the DB Code Bank. Georgev posted a really nice search.zip and I posted one which has alphabetical letters to click on for searching.

    You can also right-click on a field on the form and utilize the Filter.
    Last edited by pkstormy; 07-30-07 at 20:03.
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

  9. #9
    Join Date
    Jun 2004
    Location
    Seattle, WA
    Posts
    601
    My brain is fried and I am doing something wrong. If I leave it unbound, I get a bad type error. If I bind it or format the txtbox it says can't go to that record.
    Ryan
    My Blog

  10. #10
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926
    What is the field type for the data you are searching on? (ie. is it the primary key field, record identifier or a text value field) Is it a combobox or just a text box?

    You can try this:

    dim MySearchValue as String <- or Variant, or Date, or Integer depending on what kind of value you're searching on
    MySearchValue = me!txtSearch (which is the same as: MySearchValue = me.txtSearch.value)

    me.MyBoundFieldToSearchOn.setfocus <- I usually use the docmd.FindRecord if I'm finding a value but I have to go to the bound data field on the form first. Once I go the bound data field (ie. set the focus) then I can search through the data in that field for my search value.

    docmd.FindRecord MySearchValue
    verses

    DoCmd.GoToRecord , , acGoTo, MySearchValue
    or...
    DoCmd.GoToRecord , , acGoTo, Me.txtSearch.Value <- this might be why you're getting the error if me.txtSearch.Value is possibly a string value.

    Again, using the docmd.FindRecord command, I First set the focus to the bound field on the form I'm matching the search value with before I issue the FindRecord command. After the docmd.FindRecord line, I then also put a me.MyOtherDataEntryField.setfocus (or even better - me.SomeCommandButton.setfocus). This way, the user doesn't accidently clear the bound data field after they do the search.

    I'm not totally sure on using the docmd.GoToRecord but I think you need to give us more detail on how the search box is set up. I've always supplied a number as a search value to the docmd.GoToRecord command.

    Leave your search box unbound though.
    Last edited by pkstormy; 07-30-07 at 22:10.
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

  11. #11
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Why not use the wizard to help you on this? Command button, record navigation... You can use some funky built in find dialogue if you fancy!
    George
    Home | Blog

  12. #12
    Join Date
    Jun 2004
    Location
    Seattle, WA
    Posts
    601
    Ew George that is ugly. It may have to do if I can't get the other one going.

    Paul - The field is a date, not a PK. I was just doing a textbox since a combo could get ugly. I got your code to work.
    Ryan
    My Blog

  13. #13
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926
    Ryan,

    Just remember on Date searches, you "may" have to use # around your searchvalue variable. (ie.. MySearchValue = "#" & me!txtSearch & "#" or docmd.FindRecord "#" & MySearchValue & "#"), especially in sql strings, but I'm glad you were able to get it to work.

    Also, for sql syntax:
    ' is for string values (ie. = ' " & SomeValue & " ' ")
    " is for integer values (ie. = " & SomeValue & " ")
    # is for date values (ie. = # " & SomeValue & " # ")

    (note: I spaced out the ' " above so you can see what is what ('").

    Still, check out some of the search methods in the DB Code Bank if you get a chance.
    Last edited by pkstormy; 07-31-07 at 14:27.
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

  14. #14
    Join Date
    Jun 2004
    Location
    Seattle, WA
    Posts
    601
    Paul, I put the dim as a date, so wouldn't that make it smart enough to not need #'s? Should I put MySearchValue = "#" & Me!txtSearch & "#" just incase?
    Ryan
    My Blog

  15. #15
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926
    When you dim a variable as a date, depending on what you're doing determines if you need the # or not. In some circumstances you don't need the #. In others (like for a sql statement), you do. I was just giving you a heads up on it. If it works ok without it, leave it.

    Also, you can utilize the IsDate() function in the AfterUpdate to determine if it's a valid date entered which may save you from getting some errors when bad dates are entered.

    ie..
    if IsDate(me!txtSearch) = true then...
    Last edited by pkstormy; 07-31-07 at 15:26.
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •