Adding a record based on the value of a textbox (1 Viewer)

[str33ty]

i think i'm being really stupid! how would i go about doing this?

is it like:

[B][COLOR=black][B][COLOR=blue][COLOR=black][COLOR=blue][COLOR=black][COLOR=blue][COLOR=black][COLOR=blue][COLOR=black][COLOR=blue][COLOR=black][COLOR=red]INSERT INTO tblCubBadge (cubID, badgeID, status)
VALUES (frmEditCub.cubID, 72, earned)
WHERE frmEditCub.totalhikes >= 20[/COLOR][/COLOR][/COLOR][/COLOR][/COLOR][/COLOR][/COLOR][/COLOR][/COLOR][/COLOR][/COLOR][/B][/COLOR][/B]

i semi-got this off a website..is it in the right direction?

 

[georgedwilkinson]

Yes, that is the right general direction. All I really care about is all your table and column names.

This should get you a little closer:

INSERT INTO tblCubBadge (cubID, badgeID, status)
VALUES (frmEditCub.cubID, 72, earned)
WHERE frmEditCub.totalhikes >= 20
and cubID not in 
(
select cubID from tblCubBadge where badgeID = 72
)

I prefer not to use so many hard codes but this is the right general direction. You don't want to insert something that has already been inserted.

Next issue is, I notice you have a status column. That would imply that a cub-badge record may already be in the database with a "not earned", "pending", or other kind of status. Well, if it's already there, you don't want to insert another record, you'll need to change the "status" to "earned". Is that logic correct?

 

[str33ty]

the way the status column works is that the badge can have a status of Earned or Received. When i add the badge to the cub, it gets the status Earned and then once given to them, i would change it to received.

This then enables me to create reports detailing cubs that have earned but not recieved their badges - which then helps me when i am ordering which badges.

So when this query adds the badge, the status just needs to go to Earned so it shows up on my report.

Edit: i can't save it as it is saying there is a missing semi-colon, i tried adding one anywhere and everywhere, bit it still wouldn't let me save it.

 

[georgedwilkinson]

The semicolon goes at the end of the statement, like this:

INSERT INTO tblCubBadge (cubID, badgeID, status)
VALUES (frmEditCub.cubID, 72, earned)
WHERE frmEditCub.totalhikes >= 20
and cubID not in 
(
select cubID from tblCubBadge where badgeID = 72
);

 

[str33ty]

still has the same error!

 

[str33ty]

sorry to bring an old thread back from the dead, but i've been trying for ages and i still can't get this work! any help?

 

[str33ty]

does anyone have any clues?