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

[str33ty]

Hello

I am currently constructing a database to keep track of my local cub scout groups badge achievements.

There are badges available for how many nights away the cub has completed, these are staged in 1, 5, 10, 20, 50 etc.

I have a box attached to each record that keeps track of how many nights away they have done with us. What i would like to do is have a macro or code that automatically creates a record in tblBadgeCub with the 'earned' status next to the appropriate cub.

I have 3 tables:
tblCub - details of the cub
tblBadge - this is just two columns, badge name and badge ID
tblBadgeCub - this has 3 columns, cubID, badgeID and status (whether they have received or just earned their badge and not yet recieved it)

can it be done?

 

[georgedwilkinson]

Yes, it can. Sounds like you've got the fundamentals.

 

[str33ty]

Ok, how would it be done?

 

[georgedwilkinson]

I'm sorry, I don't understand the question. Wouldn't you just create a form/subform to assign the badge to the cub?

You can see a M:M example here:
Many-to-many example

 

[str33ty]

i have got the form to do that, but i would like it to do it automatically, so i don;t have to keep flicking between the two forms

 

[str33ty]

anybody got any clues on this?

 

[dkinley]

Not sure which table this box is that keeps track of the nights away but could do something like the following on the OnUpdate() event of the box.

Dim sStatus As String
 
Case Select Me!txtBoxNameThatTracksNights
 
Case 1 TO 4
     sStatus = "Newbie"
Case 5 TO 9
     sStatus = "Rookie"
Case 10 TO 19
     sStatus = "Motivated"
Case 20 TO 40
     sStatus = "Hard Charger"
Case 50 TO 99
     sStatus = "Veteran"
Case Else
     sStatus = "Grizzly Adams"
End Select
 
    DoCmd.RunSQL "UPDATE tblBadgeCub " _
        & "SET tblBadgeCub.status =  '" & sStatus & "' _
        & " WHERE ((cubID) = " & Me!txtcubID &  AND (badgID) = " & Me!txtbadgeID & ");"

Again, this is untested and if I know me, which I do, the SQL statement will have to be tweaked a bit.

The multiple WHERE clause is for correct update (the right person and the right badge) since the record is kept on a table apart from the others.

-dK

 

[dkinley]

I was assuming the record already existed and you just wanted to update it ... if you want to make the record, use the SQL INSERT command.

If you want it to do both ... you will need to put in some conditional statement prior to execution ...

Dim sStatus
 
Select Case
    'Case choices
End Select 
 
If recordexists then 
     'Do the update
ElseIf the record doesn't exist
     'Do the insert
Else
     'msgbox system error
End If

-dK

 

[str33ty]

no the record does not yet exist, thanks, will have a play round with it and get back to you

 

[georgedwilkinson]

I'm not sure how you expect any piece of software to generate data from nothing.

 

[str33ty]

it wouldn't bee from nothing though, the cubs record would be open (which would contain the cub ID) and the badge has a constant ID (say, 25) so when the number goes over 5, i would like it to create a record with the current cubID and a predefined badgeID with a predefined status.

 

[georgedwilkinson]

Oh, in that case, see: Many-to-many example

 

[str33ty]

i've got a many to many relationship set up with a table in the middle, all i want this code to do is create the record.

 

[georgedwilkinson]

Based on what? Code cannot create a record without some type of user input telling it what data to input. And since Pat's M:M sample displays how to get Access to do all that without code, why bother writing code?

The simple answer is, create a subform with the "junction" table as the subform. Use a combo box to select the badge name, which inserts the "hidden" badge number in the junction table. As automatic as you can get, I imagine. It is perfectly demonstrated by Pat's sample.

 

[str33ty]

i have all that, all i was wondering was if there was an easier way. If i have 30 cubs that all spend 5 nights with us, it means i have to go through each record changing the amount of nights spent and then go to the sub form and add each badge to each child, i was merely wondering if this could be automated.

 

[georgedwilkinson]

Based on what? How are you going to supply to the database that Johnny got his "Helping old ladies across the street" badge? The database doesn't know to automatically update itself that Johnny got that badge without you telling it so. How do you propose to tell it?

 

[str33ty]

I appreciate that this is hard, and i am probably explaining it in a backwards way, let me take a couple of screenshots and walk you through waht i would be doing...thank you for your time!

this isn't for every badge, only for the badges for how many nights they have spent away with us.

For example:

Miles here has done 19 nights away with us:

now if we run a camp which is one night away, he would then go up to 20 - eligible for a badge '20 nights away'

now if this is just one cub, then that is alright, i click the +1 which takes it up to 20, then head over to my 'cubbadge form' and add the appropriated badge, with the status of earned, as we do not give them to them straight away:

now, if it just one cub who earns the badge, then there is obvioulsy no problem, but what if 30 of thoe cubs came to the camp and earned the 1, 5, 10, 20, 35 or even 50 nights away badge, thats a lot of work for one man with work which could surely be automated.

For reference, adding the badge creates a record in the cubbadge table:

hope that helps! sorry for the confusion

 

[georgedwilkinson]

AHA! You can write an insert query to do this semi-automatically. Then you can invoke that query on the change event of the total nights text field.

Try your hand at the query and let us know when you run into trouble, along with the SQL of what you have so far and a description of why it doesn't work right and what would make it work right. I could probably knock the query out for you in about 30 seconds but I don't have the table layout/column names.

 

[str33ty]

you're a legend, i'm really rusty with database (did it last properly about 3 years ago, what i've done so far in several days you could probably do in about 2!)

can you walk me through (in english, i'll try and translate it to sql) what exactly i need? where i need to specify etc.

[i thought it could be done ]

 

[georgedwilkinson]

Go into the query editor and create an insert query (use the menus/designer). Get as close as you can and then go to SQL view of your query. Paste that here (that'll hopefully give us an idea what tables/columns you are working with).