Design Advice (Access 97)

Topic

New Reply

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!

= 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

Reply | Quote

Replies

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.

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

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”

Reply | Quote

Just out of curiousity, wouldn’t it be easier to work with a bound form?

Reply | Quote

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.

Reply | Quote