Results 1 to 12 of 12
  1. #1
    Join Date
    Jan 2010
    Posts
    13

    Unanswered: Can you add a new record without an autonumber?

    Hi all,

    This is probably a stupid question, but I wanted to modify my 'Add Role' button code to first check for duplicates. Right now I am just having trouble with the code to add a new record.

    My Role table structure is as follows:
    RoleID -> Autonumber
    RoleName -> Text

    I could be wrong but I think my issue lies around that I am not inserting a RoleID into the table (nothing is added with my code below). I wanted to hide the RoleID from the user, but i'm not sure how I can insert without adding an ID into my code. Any ideas?

    Private Sub AddRole_Click()
    Dim dbResource As DAO.Database
    Dim rstRoles As DAO.Recordset

    Set dbResource = CurrentDb
    Set rstRoles = dbResource.OpenRecordset("Role")

    rstRoles.AddNew
    rstRoles("RoleName").Value = RoleName.Value
    rstRoles.Update

    End Sub

    Thanks!
    Last edited by adamcm; 01-18-10 at 15:51.

  2. #2
    Join Date
    Jan 2010
    Posts
    13
    Stupid of me...but I figured although the Id is hidden I can still insert it.

    But I still am having issues with the code. No records are being added using the code above (with the ID added in as well).

    Am I missing something in the VBA environment?

  3. #3
    Join Date
    Nov 2009
    Posts
    49
    OK this is going to be kinda long so sorry if i put a small mistake in:

    Dim dbResource As DAO.Database
    Dim rstRoles As DAO.Recordset
    Dim strRole as string

    Set dbResource = CurrentDb
    Set rstRoles = dbResource.OpenRecordset("Role")

    strRole = RoleName.value

    rstRoles.MoveFirst 'this goes to the first record in the Role table
    rstRoles.FindFirst "[Role field in table] =" & strRole 'this looks for a duplicate
    If rstRoles.NoMatch then
    strRole = "'" & strRole & "'"
    Currentdb.Execute "INSERT INTO Role (RoleName) " & "Values(" & strRole & ")"
    End If

    I've left the Insert into like this as you might have to had more to the table than just a Role Name. If you did first:

    1. make sure that whatever you want to add has a ' either side of it (strRole = "'" & strRole & "'")

    2. Add field name into brackets after INSERT INTO Role, also make sure this is the order of the fields are the same as the table.

    3. Then include the value in the Value bracket by putting in... , " & strWhatever & " straight after the the second & and include that comma.

    Hope this helps
    Phil

  4. #4
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    I would do this all in SQL. Several reasons:
    1) More efficient (especially if you have a Unique index on RoleName, which you should since this is the natural key)
    2) More portable (The sort of SQL you write for this is standard across all RDBMSs)
    3) Less code (therefore less prone to bugs)

    So as such the code is simply:
    Code:
    Currentdb.Execute "INSERT INTO Role (RoleName) Values('" & strRole & "') WHERE NOT EXISTS (SELECT NULL FROM Role WHERE RoleName = '" & strRole & "')"
    No need for recordsets and other objects.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  5. #5
    Join Date
    Jan 2010
    Posts
    13
    Thanks! I am beginning to hate recordsets

    another stupid question. Each time I add a new Role, I am required to hit refresh all to be able to see it updated. Is this normal? It does not seem to happen when I am adding my 2nd, 3rd, etc to the Role table through my form.

    Thanks!

  6. #6
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926
    Check your tab order on the form.

    IF (IF!!!) you have an autonumber field (which I STRONGLY encourage in every data table), the autonumber is automatically generated when you enter data into a field according to the tab order and it will automatically refresh - (ie. show your ID (autonumber) field on the form to see when the autonumber is actually generated - THEN hide your autonumber field and code accordingly).

    IF (again, IF!!!) you DON'T have an autonumber field, THEN you can have problems on the form and must do stupid things like issue commands such as docmd.saverecord (which I've NEVER had to do in 25 years because I ALWAYS have an autonumber field but I often see other developers struggle with updating records on a form because they DON'T have an autonumber field - again, check tab order on the form).

    A Refresh command basically checks the criteria (ie. required fields) and will try to save the record (ie. it will try to save the record and fire events.) (I think of a Refresh command "kind of" like a me.requery command except it stays on the same record.)

    Note though - you're using DAO versus ADO code so I can't vouch for DAO coding but the concept is the same.

    You may also want to add a msgbox "I'm here" or msgbox "Value X = " & me!ValueX
    to check when events fire and what is actually going on.

    But you shouldn't need to issue a Refresh command unless you're coding in a special way or need to try and force a "save" to the table to fire an afterupdate type of event. I will use the Refresh command when I need to update the current form so if another opened form (based on the same recordset), the current form is updated and prevent the "data has been changed do you want to save changes...." type of error (not sure of the exact wording but you've probably seen this kind of error which is essentially useless no matter what you click.) - ie. avoid having multiple forms open based on AND updating the same recordset!! - THIS CAUSES PROBLEMS!!

    Otherwise, the normal tab order takes precedence and the autonumber will automatically be created when MSAccess determines the record can be updated/saved (again, show the autonumber field on your form and WATCH when the autonumber is generated - this is key to your coding!)

    I can't stress enough though on having an autonumber field in your table. I've had to troubleshoot many, many forms because there was no autonumber field and the record doesn't save correctly because there isn't one! (or there's work-around coding involved!) Without an autonumber field and trying to open multiple forms updating the same recordset causes many problems.

    A good rule - don't have multiple forms open updating the same record/recordset and keep your forms based on 1 table (ie. don't have a form based on a bunch of relational joined tables). - ie. keep your forms simple and based on 1 table.

    Recordsets are not horrible to work with as long as you realize what form(s) are open and updating it AND what order of events (ie. tab order) are happening on a specific form when updating the data. Note: also check to make sure in your tab order, that it's not the last field and trying to go to a NEW record.
    Last edited by pkstormy; 01-19-10 at 23:12.
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

  7. #7
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    If you're using the piece of code provided by pootle flump, simply add this line afterwards:

    Me.Requery
    Have a nice day!

  8. #8
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Quote Originally Posted by adamcm View Post
    Thanks! I am beginning to hate recordsets
    Don't hate them - just use them judiciously.

    I like woodwork - I think of SQL like a hammer or screwdriver - you need to use it all the time. Recordsets are like a sliding bevel. You don't ever really need one and you rarely use it, but when you do have use for it it is very useful indeed.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  9. #9
    Join Date
    Jan 2010
    Posts
    13
    Hi all,

    Can anyone see anythign wrong with this code?

    The record is added to the Table, but it is not displayed in the form.

    ie. If I add a "TEST" value and click Add, it is displayed in the table. If I remove my current value, replace it with a "TEST2" value, and click Add it is also displayed in the table. When I click on the 'Previous Record' button, "TEST" is not displayed", it is the item prior to "TEST". The table contains both, but the form does not. If I add a Me.Requery to the function, I receive duplicates.

    Thanks!

    Dim dbResource As DAO.Database
    Dim rstRoles As DAO.Recordset
    Dim strID As Integer
    Dim strRole As String
    Dim strSQL As String
    Dim recordCount As Integer
    Dim strTest As String

    Set dbResource = CurrentDb
    Set rstRoles = dbResource.OpenRecordset("Role")
    strRole = RName.Value
    recordCount = rstRoles.recordCount

    If recordCount = 0 Then
    CurrentDb.Execute "INSERT INTO Role ([RoleID],[RoleName]) Values('" & strID & "', '" & strRole & "')"
    Me.Requery
    Else
    strTest = Nz(DLookup("[RoleName]", "Role", "[RoleName] = '" & RName.Value & "'"), "nouser")

    If strTest = "nouser" Then
    rstRoles.AddNew
    rstRoles(1) = strRole
    rstRoles.Update

    Else
    MsgBox "This Role already exists in the Table"
    End If
    End If

  10. #10
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    I'm afraid I haven't messed with bound Access forms for some time. However, if you are getting duplicates then there is another problem going on in addition to what you discuss.

    For a table of this schema:
    Roles {RoleID, Role}
    There are two candidate keys. A primary key (the term you are probably used to) is just an arbitrary assignment we make to one of our candidate keys flagging it as the "main" key. There's actually some thinking in relational theory that assigning a primary key is senseless however it is standard practice to do so. Once you assign a Primary Key your other candidate keys are called alternate keys.

    So - you have said RoleID is your primary key, which means it must be populated with unique data. However, exactly the same is true of Role. This must also be populated with unique data. As such, in your table you should implement this constraint.

    Action: Create a unique index on Role and do not allow NULLs. This enforces your key and prevents duplicates. It is actually the same as a primary key constraint (unique data and no NULLs). This is known as a declarative constraint i.e. You declare the constraint in the table definition and the SQL engine takes care of enforcing it. The point of all your code above is merely to intercept the inserting of duplicates and handle these gracefully, not to enforce the constraint itself.

    If you are keen to follow up on this try googling the terms above, as well as surrogate keys and natural (or intelligent) keys.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  11. #11
    Join Date
    Jan 2010
    Posts
    13
    Thansk for the help. Just getting a little stressed!

    I just want to simply keep the same default Add button functionality, but add a duplicate check into my code. I have no idea how to do it properly and keep screwing up something small.

    Any ideas?

  12. #12
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926
    There are multiple methods to do this. One way (of many) is to make a primary key field and on your data entry field, you combine field(s), such as FirstName & LastName when updated on the form, and save this to the primary key field (thus not allowing duplicates) which would produce an error when trying to duplicate. Another method is again, to write a function to return true or false depending on the fields you want to check as I showed in the previous post. Another way is use the dlookup command to see if a similar record exists or show a listbox or other form that displays data that is similar to what is entered. Or simply show a form that has duplicate values and let the user decide which one should be deleted.

    It's difficult to say which method works best for you unless we are actively involved in your coding and db setup but I can only suggest that you try different things as suggested on a sample db, look at how others do it (there are examples in the code bank) and then apply what is best to your active db.

    I might suggest looking at some examples (again, code bank). There are many different ways I personally have coded for not allowing duplicates. It's not always the easiest when your dealing with customer type db's or other criteria since each db is setup to meet certain needs that are not always the same. (ie. you can have a John Jones in Madison, WI and also a John Jones in Dane, WI. Or 2 John Jones in Madison, WI, one on 1st street and one on 2nd street.) Unless you've extensively worked with this kind of matching, it can get tricky. Again, dlookups can come in handy.

    Depending on how your criteria is setup and the functionality of your db (ex: area specific or global or....) dictates how you actually design the db to work for the needs of the company. There's no need to go overboard on coding but you do want to meet the needs of the type of data entered for your program (phone number fields if required as good fields a primary key candidates.). For example, why design a complex duplicate checker if you know your entering data for only a specific region/state/city which has a limited dataset. Or don't even put in any kind of duplicate checking and simply show a "Find Duplicates" query to show where data has been duplicated (and let the user decide which record should be deleted.)

    As for myself and my exprience, I've found the easist way is to simply design a function which returns a true or false (as shown above). If the match = true, then I allow the user to go the matching record (deleting the exisitng edited record), or ignore and continue entering data on the new record.

    You may want to browse some of the examples in the code bank. This is one of those types of items that can become very time-involved in JUST the disucssion phase.
    Last edited by pkstormy; 01-20-10 at 22:55.
    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
  •