MS VBA Access Insert Into value and function help request

Question

I am new new to VBA. I am trying to insert data from one form to a table. I have run into a problem with the following code:

strSQL = "Insert into [KitInfo](KitID, KitType, ExpirationDate) values(Forms!CreateNewKit!KitID, 'HS2000', dmin([ExpirationDate]))"

I am trying to insert a minimum date from the current form to the "ExpirationDate" column table KitInfo. Is there a way of calculating the minimum date and then inserting that into the "ExpirationDate" column? I was thinking nesting a subquery but I couldn't figure out how to do it. I have tried changing "values" to "select", but then this query doesn't run at all.

Answer 1

DMin requires you tell it where the data resides. Use the name of a table or saved query for the function's second option, domain. You can review the Application.DMin Method topic in Access' help system for further details.

Create a query in the Access query designer to work out the syntax.

INSERT INTO [KitInfo]
(KitID, KitType, ExpirationDate)
VALUES
(Forms!CreateNewKit!KitID, 'HS2000', DMin('[ExpirationDate]', 'YourTable'))

Replace YourTable with the name of your table.

After you have the INSERT working correctly in the query designer, update your VBA code to create the same statement.