input value and calling the specific value inside MS access table

[Asher David]

Hi,

How can I put a value inside the table "ASHER" row1 column10 in the MS access using VBA codes. And also how can I recall the value inside the table "ASHER" row1 column10.

What I want to do is like this:


If a> 10 then
' put value text - "DAVID" in table "ASHER" row1 column10
End if

ANd for recalling/referring what value inside the table "ASHER" row1 column10 then I will just simply close the form or whatever.

Thanks

[bobp0303]

Hi,
To generate the code, I created a table called "asher" with two text fields "dummy" and "d1" and one numerical field "ID" that I also used as the key index field.

Suggest you attack the problem as two simple sub-problems:
1) If the row already exists:
a) Create a QBE (query in design mode) that selects the row to be updated.

SELECT asher.dummy, asher.d1, asher.ID
FROM asher
WHERE (((asher.ID)=0));

b) change the query's type to be an UPDATE query and insert the desired information.

UPDATE asher SET asher.dummy = "another dummy entry"
WHERE (((asher.ID)=0));

2) if the row doesn't already exist, create an APPEND query to append the information.

INSERT INTO asher ( dummy, d1 )
SELECT "dummy value" AS Expr1, "d1 value" AS Expr2;

In this way I reduced your real problem to a very trivial problem. To use the SQL code within a module, you can use the following code as a model:

Sub jj()
Dim SQL As String

    DoCmd.SetWarnings (WarningsOff)
    SQL = "UPDATE asher SET asher.dummy = 'another dummy' entry WHERE (((asher.ID)=0));"
    DoCmd.RunSQL SQL
    DoCmd.SetWarnings (WarningsOn)
End Sub

Note that all double-quotes within the queries have to be changed to single quotes when using the query in your module.
Set warnings off before you execute SQL statements (and on immediately thereafter) so you don't get the warning MsgBox "You are about to update x rows..."
Note that there's no error trapping code in the subroutine, blah blah

Hope this helps,

Bob.

[Asher David]

hi Bob let me try it...

But then my another question. How can I recall the value inside the specific table. like in excel we can get the value for example like a =cell(1,1).value.

How can I do it in MS access?

[bobp0303]

I don't know how to do it properly! -- My kludge has been to create a form with a single text box and populate that text box with the results of a query. This involves opening the form and closing it, which results in two extra screen updates -- looks terrible on slow machines or when people insist on running the application from zip disks. If you come across a better way, I'd love to use it!

Bob.