Insert into where

[Richie27]

HI ALL,



Thought this would be simple but it turns out it's not.

I want to Insert INTO Alphabet (A,B,C) VALUES (1,2,3) based on an entry I extract from a form.

so I tried Insert INTO Alphabet (A,B,C) VALUES (1,2,3) WHERE Alphabet_id = [Forms]![frmAlphabet]![Alpha_id]

It doesn't work. Seems you can't use 'where' like this. Any ideas?

Thanks guys,

[jzwp11]

Is the frmAlphabet bound to the table Alphabet? Could you please provide the names & datatypes of the fields in the Alphabet table?

[Richie27]

Well, the fields on the form are not actually bound to anything and is called frmCheckout. the fields are txtBoxNo, txtCheckedOutBy, txtReasonout - they are all simply text fields.

I basically have a file table and a file_checkout table. The file_checkout table records all instances where a file was checked out.

All of the files in the file table are associated with a box_id for the purposes of recording all of the files that are in a particular box.

So, what I want to do is use the unbound form 'frmCheckout' to check out all the files that are in a particular box simultaneously - based on them having a box_id which is retrieved by accessing the txtBoxNo field. I want to populate su_file_checkout based on the box number input into frmCheckout.

What I have come up with is a three stage process which is as follows:

1.Get all files in the box based on the form input box txtBoxNo
2. Stick em in an array
3. Create a loop which inputs a record for each of those files.

However, my VBA is weak so I'm struggling a bit and any enlightenment would be super!

[jzwp11]

OK, I would probably go with a structure like this (hopefully yours is similar)

tblBoxes
-pkBoxID primary key, autonumber
-txtBoxNo

tblFile
-pkFileID primary key, autonumber
-fkBoxID foreign key to tblBoxes
other file related fields

tblFileCheckout
-pkFileCOID primary key, autonumber
-fkFileID foreign key to tblFiles
-dteCheckOut (date checked out)
-dteCheckedIN (date checked back in) (not a fully normalized design)
-fkPeopleID (checked out by:foreign key to a table that holds people I assume
-txtCheckoutReason

1.Get all files in the box based on the form input box txtBoxNo Use a combo box based on tblBoxes (include pkBoxID)
2. Stick em in an array No need for an array; can use a SELECT..INTO append query
3. Create a loop which inputs a record for each of those files. No need for a loop, just build the append query in code using the parameters from the form and then run the query.

You will also have to take care of the possibility that a file from a box has already been checked out, so that you do not include that file in the append query.

The append query would look something like this

INSERT INTO tblFileCheckout ( fkFileID, fkPeopleID, txtCheckoutReason, dteCheckOut ) SELECT Q1.pkFileID,4,'special project',#5/1/2012# FROM (SELECT tblFile.pkFileID FROM tblBoxes INNER JOIN tblFile ON tblBoxes.pkBoxID = tblFile.fkBoxID WHERE tblBoxes.pkBoxID=1 AND pkFileID NOT IN (SELECT fkFileID FROM qryCheckedOutFiles)) as Q1

I have attached an example database that has the structure I indicated above, as well as the checkout form and the code to construct & run the query.

qryCheckedOut files is a query in the attached database that finds all files that have been checked out (i.e. no checked in date entered)

[Richie27]

Very detailed response - thanks! Will give it a crack!

[jzwp11]

Please post back with any questions.

[Richie27]

I will surely! Looks like the business though. Wow - never expected such a response so quickly. Have you much experience with Access/VBA or are you just a genius?

[jzwp11]

I have been working with Access/VBA for over 10 years. Pretty much taught myself with a couple books and searching forums like this.

[Richie27]

That worked really nicely!

I really like the way you name stuff as well - like your pk, txt, fk flags etc. Makes things a lot clearer.

Thanks for the lessons!!!!!!!!!!!! You have spread good karma and saved major headaches!!!!

[Richie27]

Just one tiny thing - not sure about what the Q part of the sql query does.

Wow - pretty fancy querying - you really have opened up horizons!

[jzwp11]

The Q is just the name for the query; just like you can give a field a different name (aka alias): SELECT table.fieldname as SpecialName, I figured why type a long name I'll just use Q as the alias.

[Richie27]

Ah ya, I see. Silly question in hindsight.

Thanks again!

[jzwp11]

Not at all a silly question. You're welcome

[Richie27]

I hate to ask again but I'm struggling a bit with the checkback thing. I was trying to model it on the mySQL query you gave me the last time but not quite sure what I'm doing. I just want to insert a checkback date based on the box selection in a form called frmCheckback.

[Richie27]

Okay sorry, I have this

Dim mySQL As String


mySQL = mySQL & " UPDATE su_file_checkout SET su_file_checkout.checkback_date = #" & Date & "# "
mySQL = mySQL & " WHERE su_file.box_id = su_box_location.box_ID AND su_box_location.box_ID=" & Me.cboBox & ""
mySQL = mySQL & " AND su_file.checkback_date is null "

CurrentDb.Execute mySQL, dbFailOnError

I keep getting a message box saying access can't find the table or query....weird.......