VBA coding Questions

  • Thread starter Thread starter Guest
  • Start date Start date
G

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
 
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
Click to expand...

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
 
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
 
Back
Top