Updatable Query

  • Thread starter Thread starter Warrio
  • Start date Start date
W

Warrio

Hello!

Is there a way to make a UNION query updatable?

what I mean by updatable is to let the user changes the data directly into
the fields through the query object of a form with a form which has its
recordsource set with the query

Thanks for any suggestion!
 
No, union queries are never updatable.

You would need to determine the source table of the current record, and
update that table independently of the union query. For example ...

SELECT *, "TableA" AS TheTable
FROM TableA
UNION SELECT ALL *, "TableB"
FROM TableB;

Private Sub Command8_Click()

If Me.TheTable = "TableA" Then
CurrentDb.Execute "UPDATE TableA SET TableANum = " & _
Me.txtNewValue & " WHERE TableAID = " & Me.txtID
Else
CurrentDb.Execute "UPDATE TableB SET TableBNum = " & _
Me.txtNewValue & " WHERE TableBID = " & Me.txtID
End If
Me.Requery

End Sub

Note that in this example the text box 'txtNewValue', into which new values
are entered, is an unbound text box - Access would not let you edit the
value of the text box if it was bound to a column of the union query.
 
That's what I did until now, but my data is displayed into a continuous form
and after I did the changes by code, I must do a requery to display the new
data changed. and that doesn't help me at all because of the Requery moves
the record to the top. And even if I move the cursor to the right field. it
will never be at the same place of the user's mouse because the scroll bar
place has changed and this is not clean at all!

So if you have any other idea on how update a record with keeping the right
position or to get the scroll bar position before making the changes, it
will help me a lot!

Thanks again!
 
You need to store the value(s) of the primary key field(s) before the
requery, and find the record again after the requery ...

Private Sub Command8_Click()

Dim lngCurrentID As Long

lngCurrentID = Me.txtID
If Me.TheTable = "TableA" Then
CurrentDb.Execute "UPDATE TableA SET TableANum = " & _
Me.txtNewValue & " WHERE TableAID = " & Me.txtID
Else
CurrentDb.Execute "UPDATE TableB SET TableBNum = " & _
Me.txtNewValue & " WHERE TableBID = " & Me.txtID
End If
Me.Requery
Me.RecordsetClone.FindFirst "TableAID = " & lngCurrentID
Me.Bookmark = Me.RecordsetClone.Bookmark

End Sub
 
Which I also did!!
the result I get with that is that the cursor (the black arrow on the left)
goes indeed to the right record! BUT the its position is the 1st seenable
record and there are more records before (but not seenable)
and what I want is to do not see any change into scroll bar position (there
was 2 records before and 4 after), it should stay the same after the requery

to illustrate it with a form containing 50 IDs:

Before the requery: ID 3 to 9 are seenable

Curr ID myField
===============
3
4
5
Click to expand...
6
7
8
9


After the requery (the cursor goes indeed to the right position but the
display has changed!!)
Curr ID myField
===============
5
Click to expand...
6
7
8
9
10
11



and sorry to bother you with that, but I'm just looking for a solution
(maybe if I can find how to change the scroll bar position)

Thanks again
 
I'm afraid I'm not sure of any solution to that problem. Ideally, we'd get a
reference to the first visible record before the requery, and make that
record the first visible record again after the requery. But I am not aware
of any property or method that would tell us which record is the first
visible record.

It did occur to me that possibly the work Steven Lebans has done on getting
and setting the position of the scroll bar might help here. But I don't seem
to be able to access Steven's Web site (www.lebans.com) right now. I expect
it's just a temporary problem. For now though, I'm unable to say for sure
whether it will help, but if you want to check it out, look for
"SetGetScrollBar" at Steven's Web site.
 
Thanks a lot for your help! but I'm still wonder how come you didn't have
that kindof problem before..

Best rgrds!
 
Back
Top