Need help adding Record from a form to a Table

[MadTom]

I've searched and viewed may videos and can't seem to get what I need.
I have a form with many buttons, I have a search button that will find a record, after I find the record I would like to click a button to add the current record (Cap_ID) in that form to a Projects table, field Project_Part. I would also like the button to ask quantity and to subtract that number from PartsTable. I'm trying to use et tblCustomers = CurrentDb.OpenRecordset("SELECT * FROM [Table1]")


Projects.AddNew tblCustomers![Cap_ID] = Me.Cap_ID.Value
Any help would be great!

[pbaldy]

Simplest might be to execute an append query that gets its value(s) from the form. You can execute it with

DoCmd.OpenQuery "QueryName"

Most of us wouldn't try to keep a quantity on hand field updated; we calculate it on the fly from transactions. More here:

http://allenbrowne.com/AppInventory.html

though the function may be overkill for many applications (I've never needed it).

[MadTom]

Thanks, I'll give it a try, was looking at a few videos on that direction.

[pbaldy]

No problem, post back if you get stuck.

[MadTom]

ok, got my

append query working from my form with a button. Can't seem to get the criteria to use the current recordset in my form, or do I need to use texbox? I'm also using a calculated field to get quantity in my query. Thanks!

[pbaldy]

I would expect either the SELECT clause to have form references or the WHERE clause. In either case, textboxes, yes.

[MadTom]

I can't seem to get it right? I used [Forms]![FormName]![ID] in the

criteria of my

append query, doesn't work. I'm using bound textbox from table, Name ID, Label0
SELECT "C" & [Cap_ID] AS PartName, [Capacitors].[Cap_Value] & "Uf " & [Cap_Type] AS PartQ, ([inputData]) AS QTY
FROM Capacitors
WHERE ((("C" & [Cap_ID])=[Forms]![Capacitors]![Cap_ID]));

[pbaldy]

That's not an append query, and I'm not sure what "doesn't work" means exactly. Can you attach the db here?

[MadTom]

This is the database I'm working with. I'm working on getting the add to project button to add the current recordset to the projectTest table with a Quantity input from a Append Query. Any help would be great!

[MadTom]

I see now it's not a Append Query, but I did have it, not sue what happened with all my changes.

[pbaldy]

Sorry, I tried to look at it this morning but it wouldn't open with 2010. I meant to fire up a VM with 2016 on it but got distracted by work. I'll try to get back to it, working on something right now. That pesky job gets in my way sometimes.

[MadTom]

This is what I'm working with, where did I go wrong? Label0 is the ID textbox on open form. Query will add all records if no

criteria entered. Cap-ID is auto number field, Project_Name is short text. Any help would be great! Thanks

INSERT INTO Projects ( Project_Parts, Project_Name, ProjectParts_Quantity )
SELECT "C" & [Cap_ID] AS PartName, [Capacitors].[Cap_Value] & "Uf " & [Cap_Type] AS PartQ, ([inputData]) AS QTY
FROM Capacitors
WHERE (((Capacitors.Cap_ID)=[Forms]![Capacitors]![Label0]));

[pbaldy]

As just a select query, does it pull the correct record and display the desired data?

[davegri]

You don't have any records in the Projects table yet. I think you need a form (frmProjects)to define projects before you start trying to add parts to it.
Any input to Projects table could then come from bound textboxes and comboboxes on the new form.
Also, you should define relationships between tables in the relationship diagram before any more work with forms or queries.

[MadTom]

Paul, if I run the query with no criteria it will enter all records from tblCapacitors into projects table, if I try anything in criteria I get 0 records.

Davegri, I may have gotten a head of my self, I will work on my structure and

relationships and try adding a frmProjects form. Thanks for the help!