Updating Recordsets 2

[SirTECH]

The Problem.
By using a form that's bound to a complex query, the records are not updatable. I thought that I could use a temporary table (stored in memory only) then update the permanent relevant tables as necessary. However, I haven't been able to find a way to do this.
The database is multiuser with various levels of security access, so I'm not sure that creating an actual table (not in memory only) that's deleted afterwards will work.

Basically, all I want to do is allow users to input data into a couple of fields that belong to different tables based on information gathered from several tables and queries.

Any suggested workarounds would be greatly appreciated.

 

[fneily]

Go back and make your query updatable. This may mean adding the primary keys of all the tables and making sure your relationships are ok.
Or, for those fields that are to be updated, make them Unbound. Then have, let's say, a command button to save just those fields. The following is an example using DAO to update fields.
Private Sub notebox_AfterUpdate()
Dim DB As DAO.Database
Dim RS As DAO.Recordset
Dim strWhere As String

Set DB = CurrentDb()
Set RS = DB.OpenRecordset("TableName", dbOpenDynaset)
strWhere = "[PrimaryKey] = " & Chr(34) &
Me![FormControlName] & Chr(34)
RS.FindFirst strWhere
If RS.NoMatch Then
MsgBox "No match found"
Else
RS.Edit
RS![TableFieldName] = Me![FormFieldName]
RS.Update
End If
RS.Close
DB.Close
Set RS = Nothing
Set DB = Nothing
End Sub

Substitute Tablenames and fieldnames. Also, if primary key is numeric, then use
strWhere = "[PrimaryKey] = " & Me![FormControlName]

 

[SirTECH]

Making the fields unbound was the easiest way to go. However, when the form is reopened for further editing, how can I populate the saved data into the unbound fields?
Thanks again for your help!!

 

[PHV]

Have a look here:

http://msdn.microsoft.com/archive/en-us/dnaraccess/html/msdn_harness.asp

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[fneily]

On the forms OnCurrent event, you can retrieve the data for that record by using the Dlookup function to populate the fields. Thusly: (I give two examples. One fetches an alpha, the other a numeric. You can look up the DLookup function in Help. It's very handy.)

Private Sub Form_Current()
DoCmd.Maximize
Me![AorB] = DLookup("[AorB]", "MMCMainTable", "[MMCNumber] = " & Chr(34) & Forms![Modal_Clearance_Update_Form]![MMCNumber] & Chr(34))

Me![zone1] = DLookup("[ZoningCode]", "ParcelTable", "[ParcID] = " &
Forms![Modal_Clearance_Update_Form]![ParcID] )
End Sub

 

[SirTECH]

Thanks for the very useful examples! Much appreciated!