INSERT Statement (Access)

S

Sparky Arbuckle

I'm currently writing a Windows application in VB.NET using Visual
Studio .NET 2003 and connecting to a MS Access database. The software
checks SMS and Active Directory to see if a software license is
available and, if so; writes the employee's name into tblSoftware for
that instance of software.

So let's say "MS Access 2003" was available for user "Joe Schmo". I
have 2 hidden labels that contain this data. I then set 2 variables:

How would I INSERT Joe Schmo into a table where SoftwareTitle = MS
Access 2003 and the ID (autonumber) was MaxOfID?

Would I have to create 2 queries, one to find max of ID where
SoftwareTitle = MS Access 2003, and then one to INSERT? I'm probably
missing something small here.
 
M

MGFoster

Sparky said:
I'm currently writing a Windows application in VB.NET using Visual
Studio .NET 2003 and connecting to a MS Access database. The software
checks SMS and Active Directory to see if a software license is
available and, if so; writes the employee's name into tblSoftware for
that instance of software.

So let's say "MS Access 2003" was available for user "Joe Schmo". I
have 2 hidden labels that contain this data. I then set 2 variables:

How would I INSERT Joe Schmo into a table where SoftwareTitle = MS
Access 2003 and the ID (autonumber) was MaxOfID?

Would I have to create 2 queries, one to find max of ID where
SoftwareTitle = MS Access 2003, and then one to INSERT? I'm probably
missing something small here.
Click to expand...

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Do you have another table that holds the software titles, and that is
where the software title ID is?

You can get the Software_ID like this (VBA):

lngSoftID = DLookup("SoftwareID", "SoftwareTitles", _
"Title='MS Access 2003'"))

I believe this command should suffice:

PARAMETERS UserName Text(25), Software_ID Long;
INSERT INTO tblSoftware (Name, SoftwareID)
VALUES (UserName, Software_ID)

You can pass the query parameters using VBA & DAO, or ADO. Read the
Access articles on Parameter queries for more info.
--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQxS/wYechKqOuFEgEQJbSACg2F0J/0Pqh28vzGRhqo0AGO4OgI4An1Fx
dPJ18EE1SoVTSqpF6bijIh5D
=9Hvg
-----END PGP SIGNATURE-----
 
S

Sparky Arbuckle

I just have one table that I am using for this specific INSERT.

tblSoftware
Field - ID (autonumber)
Field - SoftwareTitle
Field - EmployeeName

I want to INSERT into a field that already has ID and SoftwareTitle.
EmployeeName is NULL.
 
S

Sparky Arbuckle

Thanks for the reply MGFoster but it in no way answered what my
question was.
 
S

Sparky Arbuckle

Solved:

Function GetMaxIDBySoftwareTitle(ByVal x as String)

yadda yadda
Return MaxEmployeeID

End Function
 
J

John Spencer (MVP)

It would probably take two queries. A Select query to get the MaxOfID and then
an UPDATE (Not insert) query to update the value in EmployeeName. Also, I would
assume (maybe in error) that you wouldn't want to overwrite any record that
already has an employeename in it. If not, drop the EmployeeName is null from
the query criteria.

SELECT MAX(ID)
FROM SomeTable
WHERE EmployeeName is Null
AND SoftwareTitle = "Ms Access 2003"

UPDATE SomeTable
SET EmployeeName = "SomeValue"
WHERE SoftwareTitle = "MS ACCESS 2003"
AND EmployeeName is Null
AND ID = MAXID
 
Top