VBA coding Questions

[Guest]

This is my first time to develop database application using ms-access so
please consider me as a beginner


I need to know 2 main things
- how to get value from query and assign the value to a variable in VBA
Code
- how to get autonumber value from system table and assign to a variable
in VBA


There are 2 tables:
ENROL (sid, eid, cid)
CLASS (cid, cname, ctype, lvid, vid, coid, is_on)


I need to execute following transaction in VBA code

Dim intSID as Integer = Forms!frmStudent!sid
Dim intCID as Integer

intCID = SELECT TOP 1 C.cid
FROM ENROL E, CLASS C
WHERE E.cid = C.cid
AND E.sid = intSID
AND C.ctype = 2;

If intCID = NULL Then
intCID = (value from autonumber) /* cid; autonumber */
INSERT INTO CLASS (cid, ctype)
VALUES (intCID, 2);
End If

INSERT INTO ENROL (sid, cid) /* eid; autonumber */
VALUES (intSID, intCID);


* I am thinking about creating scalar function (returns 'cid' or autonumber
value) as a module and execute it in VBA code. Is it possible? if so, how it
can be implemented in VBA code?

Thanks for your time.


Daniel Yang

 

[AnandaSim]

This is my first time to develop database application using ms-access so
please consider me as a beginner

I need to know 2 main things
- how to get value from query and assign the value to a variable in VBA
Code
- how to get autonumber value from system table and assign to a variable
in VBA

The bridge between SQL/Query (a request) and VBA (an in-memory
calculation) is the in-memory recordset. A recordset is a bunch of
records or one record. A record can be a bunch of fields or one
field.

There are two object models you can choose from in creating a
recordset - ADO or DAO.

Have a look at http://msdn.microsoft.com and search for the these
terms.

HTH
Ananda

 

[Guest]

Hi

VBA can only run action SQL statements. If you want to extract valuies then
you should use the SQL to create a recordset, then extract the value from the
recordset. Note that if you are picking the 'TOP 1' then the WHERE statement
is all you need if the WHERE statement only returns 1 record:

Set rst = CurrentDB.OpenRecordset("SELECT C.cid etc. etc")
intCID = rst!cid

Re autonumber (say 'ID')- just add the field name into the above and extract
the same way:

Set rst = CurrentDB.OpenRecordset("SELECT ID, C.cid etc. etc")
intCID = rst!cid
autonumber = rst!ID

Hope this helps.

BW