Results 1 to 4 of 4
  1. #1
    2 Star Lounger
    Join Date
    Jun 2002
    Posts
    117
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Design Advice (Access 97)

    Unbound form with 26 mixed controls(combo, text, boolean)

    The record is saved by opening a DAO recordset and the table fields are updated. ( rst!<table Field> = Me![Control Name] ) This takes approximately 3-4 seconds for all the fields to update.

    Is there a faster way to save the record without attaching the form to the table?

    Thanks

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Design Advice (Access 97)

    You can construct the SQL string for an update query in code, then use the Execute method of CurrentDb to run the SQL. That is usually faster than using DAO.

    Here is an example of how you could insert a new record - you must of course adapt it to your situation.

    <img src=/w3timages/blueline.gif width=33% height=2>

    Dim strSQL As String
    strSQL = "INSERT INTO tblSomething ( NumericField, TextField, DateField ) VALUES ("

    If IsNull(Me![txtNumericField]) Then
    strSQL = strSQL & "Null"
    Else
    strSQL = strSQL & Me![txtNumericField]
    End If

    strSQL = strSQL & ", "

    If IsNull(Me![txtTextField]) Then
    strSQL = strSQL & "Null"
    Else
    strSQL = strSQL & Chr(34) & Me![txtTextField] & Chr(34)
    End If

    strSQL = strSQL & ", "

    If IsNull(Me![txtDateField]) Then
    strSQL = strSQL & "Null"
    Else
    strSQL = strSQL & "#" & Format(Me![txtDateField], "mm/dd/yyyy") & "#"
    End If

    strSQL = strSQL & ")"

    CurrentDb.Execute strSQL

    <img src=/w3timages/blueline.gif width=33% height=2>

    It is easy to modify this for updating an existing record. The result must be of the form

    strSQL = "UPDATE tblSomething SET Field1 = Value1, Field2 = Value2 WHERE ID = Value3"

  3. #3
    Gold Lounger
    Join Date
    Jun 2001
    Location
    Crystal Beach, FL, Florida, USA
    Posts
    3,436
    Thanks
    1
    Thanked 34 Times in 34 Posts

    Re: Design Advice (Access 97)

    Just out of curiousity, wouldn't it be easier to work with a bound form?
    Mark Liquorman
    See my website for Tips & Downloads and for my Liquorman Utilities.

  4. #4
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Design Advice (Access 97)

    Is each of these controls being written into the same record or into separate records? If the former, a bound form would be far simpler. If you're doing the latter, then you are stuck with the delay if you do them all at once. The way I've handled that in the past was with a routine called from the AfterUpdate of each control to write that record immediately. Then the delay become unnoticeable because it isn't all at once.
    Charlotte

Posting Permissions

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