Add record to different existing table

[ggodwin]

Hello all this is my first post at UA and I hope many more to come. I am new to access (zero training) and have a project to complete by the end of December.

I have two tables one table is callled "Investigation" another table is called "dipsutes".

The information in the two tables can be related. In a pefect world they always will be related. However, in some cases they are aren't. In my db project I need I am trying to design this so that I can adapt to both the perfect situation and the imperfect situation.

The "imperfect situation" is NO problem. Manual entry...finish. However, the perfect senario will be that I get the information for the "Investigation" table (enter it) and by selecting a option button or chech box it will create the record in the dispute table.

How can I do this?

I am assuming it has to be event driven. So I assume I can have a box checked and select a button "save" or "create dispute". If I could just have a button that triggers the action would be best.

My format. I am new to access and new to UA and not sure what formats are used and appreciated.
Table:[table name].[field name]

Table: [Inverstigation].[Scrap tag] » Enter into [Disputes].[Scrap Tag]
Field: [RC_occurence],[RC_Detection],[Responsible], combine these fields and enter into one field » Table: [Disputes].[Reason]

Before I get to far into this. I also want to say that I need to use the "scrap tag" number and attempt to pull some values from another table to also populate the [dispute] table. Is this possible with in the same event?
Would it be possible that on the "create dispute button event" I could also look up some values from another table?

 

[jal]

You mentioned "event driven" - sounds to me like you don't mind delving into VBA. Good. Drop a button on a new form and setup the On_Click event to do your processing. In VBA. an action query is run by

DoCmd.RunSQL "UPDATE table1 SET LastName = 'Smith' "

The syntax to insert a single record is:

INSERT INTO table1 (FirstName, LastName) VALUES ('JOHN', 'Smith')

To insert multiple records (insert all the customers into table1)

INSERT INTO table1 SELECT FirstName, LastName FROM Customers

 

[jal]

Sorry, had to edit that last post.

 

[jal]

On such a bulk insert you might also need to name the fields, so let's rewrite it like this:

INSERT INTO table1 (FirstName, LastName)
SELECT FirstName, LastName FROM Customers

You can rename a column on the fly:

INSERT INTO table1 (FName, LName)
SELECT FirstName as FName, LastName as LName FROM Customers

 

[jal]

Before I get to far into this. I also want to say that I need to use the "scrap tag" number and attempt to pull some values from another table to also populate the [dispute] table. Is this possible with in the same event?

Any SELECT - no matter how many tables and joins involved - can be used to supply the record-info for an INSERT, as long as that SELECT supplies data appropriate for insertion into the destination table. Again, just use the

DoCmd.RunSql "INSERT INTO table1 SELECT...FROM...."

 

[jal]

Forgot to mention,you might also want to set:

DoCmd.SetWarnings False


if the warning messages begin to annoy you.

 

[boblarson]

Forgot to mention,you might also want to set:

DoCmd.SetWarnings False


if the warning messages begin to annoy you.

BIG CAVEAT here -

In VBA, if you don't turn them back on, they won't be on at all for any of your databases.

So, you should ALWAYS include

DoCmd.SetWarnings True

after you are done with your stuff. AND you should stick that as your first line in an error handler in any procedure where you turn the warnings off.


By the way, you don't need to turn the warnings off if you use

CurrentDb.Execute SQL, dbFailOnError

method.

 

[jal]

Thanks, Bob, I was completely unaware of all that - I'll add it to my notes.

 

[ggodwin]

Jal & Bob,
Thanks for the help.
Sorry this has taken me soooo long to get to. When problem happen projects get dropped off.

Can you show me an exact example of the code I might need. I am all for VBA but am not real keen on the syntax.

I have seen some SQL commands that resemble what you have given me but I think they will be different. Getting to the VBA to exectue it correctly is anothe issue.

 

[ggodwin]

Here is more information.

Basically what I am doing is manually selecting the tag numbers that need a dispute record added. The information the user will enter in the investigation table will dictate what needs a dispute and what doesn't.

Based on the information the user can hit the addDispute button and the record will be created by the information from the master file by using the tag number of the current record as the source.

So I need the VBA to look at the tag number and use it look up fields on the master table to populate the dispute table.

Review...
I am manually entering information into a form/table named [Investigation]
in this table I will enter a field called (Tag Number)

(Tag Number) is a unique number that is defferent for each record in my master data table called [DAILY_SKPI_UPDATE]

On the investigation form I have a command button named "addDispute".

while entering on that form I want to be able to use the tag number to pull other fields from the master table [DAILY_SKPI_UPDATE] and insert them into the table called [DISPUTES].

 

[jal]

There may be a lot of steps involved here. Maybe I can give you some tips on one of those steps.

Design a SELECT query and, if you like omit the WHERE clause
WHERE tagNumber = whatever 'omit the whole WHERE clause
You can drop a subform control onto your form as to display data- but don't do it yet because the idea is that it displays another form, so create a new form (you can call it subform1) and set the form as AutoForm: Columnar and base it on the query you created. Then drop the subform control onto the mainform and, in the wizard, designate suform1 (the form you just made) as the form.

Then drop a textbox called txtTagNumber on the form, and a button called btnLookUpTagNumber (cancel out the button wizards) and then go to the button's Event On_Click and choose EVentProcedure and then click the ellipsis to open the

private sub btnLookupTagNumber_OnClick()

Dim qry As QueryDef
Set qry = CurrentDb.QueryDefs("qryTagNumbers") <------- name of your query object
qry.SQL = "Select & from TagTable WHERE tagnumber = " & txtTagNumber.Value
Me.subform1.SourceObject = "Query.qryTagNumbers"
Me.subform1.Requery

End sub


That should display your data onscreen. There's better ways I think, but I'm a beginner.

 

[jal]

sorry i updated by accident give me a moment.