Inputting data in forms and updating tables with a button

[FurryGorilla]

I am trying to add records to a table using a combination of a combo box, text boxes and a single button. The form has 2 text boxes called RetentionDate and Form_num, 1 combo-box which uses a query to list eligible records, and a button to add records. When you select a record from the combo box the primary key is displayed (called Eartag_num). The button then needs to be used to update 3 tables based on the inputted data.

[ol][li] CATTLE table which updates one field with a numerical value of 1.[/li]
[li]CLAIM table which needs to be updated using the RetentionDate and Form_num text boxes from the form.[/li]
[li]CLAIMED table which needs to have the Form_num and Eartag_num added to it.[/li][/ol]The command I have on the form button is as follows:

DoCmd.OpenQuery "qryUpdateBull", acNormal, acEdit

Where qryUpdateBull is the query I wish to run.

The SQL for the qryUpdateBull is as follows:

UPDATE CATTLE, CLAIMED, CLAIM SET CLAIMED.Eartag_num = [Forms]![BULL PREMIUM]![Eartag_num], CLAIMED.Form_num = [Forms]![BULL PREMIUM]![Form_num], CATTLE.NumberPremiums = "1", CLAIM.Form_num = [Forms]![BULL PREMIUM]![Form_num], CLAIM.RetentionDate = [Forms]![BULL PREMIUM]![RetentionDate];

Although Access did come up with the following:

UPDATE CATTLE INNER JOIN (CLAIM INNER JOIN CLAIMED ON CLAIM.Form_num = CLAIMED.Form_num) ON CATTLE.Eartag_num = CLAIMED.Eartag_num SET CLAIMED.Eartag_num = [Forms]![BULL PREMIUM]![Eartag_num], CLAIMED.Form_num = [Forms]![BULL PREMIUM]![Form_num], CATTLE.NumberPremiums = 1, CLAIM.Form_num = [Forms]![BULL PREMIUM]![Form_num], CLAIM.RetentionDate = [Forms]![BULL PREMIUM]![RetentionDate];

Unfortunately when I click on the button no rows are updated and being a novice I’m not entirely sure why.

Thanks in advance,
Chris

 

[jakar]

Hi there

When adding records to a table using SQL you have to use:

INSERT INTO target [(field1[, field2[, ...]])]
VALUES (value1[, value2[, ...])

Hope this will work for you

Happy eastern

Jan Karlsson
janne@karlssons.net

 

[FurryGorilla]

Ta very much,

I actually managed to come up with that just after I posted, eventually got my head round it

Just a couple more questions though.

When I insert the form number and retention date into the CLAIM table is there a way of checking using SQL to see if the form is already present so that I'm not adding the same record again (as this is obviously the wrong thing to do)?

Can you refresh the combo box once the record has been added so that the user cannot add that animal to the table again?

Oh and if you've still got time is there a way to display the animals on the form using a subform and a query as soon as the button has been pressed?

Many thanks
Chris

Oh and a Happy Easter to you aswell

 

[Donkeygirl]

You can do all of this after the update. Refresh the list and open subform. Look at the subform wizrard. Once set up you can modify it to open after refresh of the data. Also, if someone enters in the same data into a field, you can put a validation rule in the property box of the field to evaluate ' if input = current data then msgbox, clear field set focus, else tab next' Hope this helps some. It is vague, but look some of it up and you should get the idea. Depending on the version you should ferer to the help as much as possible. I love 97 help, cant stand 2000
good luck! :-Q