accessing and editing data directly?

[TacoBueno]

Can someone help give me a general idea of how the sql code, the vba code, and access's gui objects work together? Specifically how does one directly edit or enter information into the database via code only. For example, a Form has a property called RecordSource, and a TextBox has a property called ControlSource. What I am having trouble with is how the SQL code SELECT returns data for use.

If I type in

SELECT c AS bob FROM t;

into the GUI I can create a bob of an unknown type(I have no clue how to access its members other than the first one by saying MsgBox(bob), or setting its first member by bob.Value = "asdf")

but if I do something like

Me.RecordSource = "SELECT c AS bob FROM t; "

It doesn't work...

also in the first example I need to know why

bob.Value = "1"
bob.Value = "2"

will only place a "2" into my database overwriting the first one, when does the actual data transfer take place from bob.Value to a member in the database? I know it has something to do with the navigator bar at the bottom but are you even able to mess with that in code? Such as making it update to the database somehow manually(there is an afterupdate and a beforeupdate that I can access but where does the rest happen)?..

and also why can I set a text boxes ControlSource to bob through the GUI but not by

TextBox1.ControlSource = bob

anyway thanks for any help you might give, and any webpages that give good examples on how to edit data directly would be appreciated also.

 

[izyrider]

Me.RecordSource = "SELECT c AS bob FROM t;"
does work if you have a textbox having bob as .controlsource (and a field c in table t (of course))

and also why can I set a text boxes ControlSource to bob through the GUI but not by
TextBox1.ControlSource = bob
did you try "bob" instead of bob?

create a bob of an unknown type
eh? you didn't create any bob, you SELECTed some c and called them bob

or setting its first member by bob.Value = "asdf"
assuming this is a form and not a list with .boundcolumn = bob-index, then you overwrote bob in a record

i think it is ugly to use code to drive a form to perform bulk-updates on a table.
if you want to play with a bunch of bob (c),

either do it directly in SQL:
dim strSQL as string
strSQL = "UPDATE t SET c = 'fred' WHERE c = 'sam';"
currentdb.execute strSQL 'ooops - forgot the strSQL bit

or do it in a recordset:
(DAO code ! but ADO equivalent exists)
dim dabs as DAO.database
dim recs as DAO.recordset
set dabs = currentdb
set recs = dabs.openrecordset("SELECT c FROM t;")
with recs
do while not .eof
if !c = "sam" then
.edit
!c = 'fred'
.update
endif
.movenext
loop
end with
close recs
set recs = nothing
set dabs = nothing
...which i hope partially answers your how the sql code, the vba code, and access's gui objects work together question: they do and they don't - horses for courses.

depending on what you've done to your form/table, you might need a me.repaint or me.requery to display your changes.

izy