How to select an item on a subform to create a table entry?

[planetjeff]

Hello all,
I have a form showing judges and a subform showing a list of all the events the judge can, well, judge. I also have a couple buttons where I'd like to be able to work on the selected record in the subform and take those values to create a new record entry (or delete an existing entry) in an assignments table. I have read all over the place but have had no luck seeing anyone referring about this kind of problem, and I would think it'd be very common. How do I do this? Specifically:

1. How do I record the values of what the user selected in the subform?
2. How do I reference these values in the OnClick for the button?
3. How do I create a new record entry in a table given these values?

I'm assuming this all has to happen in VBscript, but I'm at a loss trying to figure this out. Thanks in advance,
jeff

 

[jedel]

Jeff,

All of these things can be done. But I could you expand your request a little.

ie

point 1. Are recording these values into a separate table? are these "Values" just a field in the subform?

Point 2. This is done in the SQL statement we will be using for this problem

Point 3. You're right, we will be using VBA, and the transferring of the data is relativel simple, But for me to give you a good answer, I would like a bit more detail.

Here's Something to think about:

Dim Field1 as string
Field1 = me.txtField
Docmd.gotoRecord,, AcNewRecord

me.txtField = Field1

Cheers

Dean


"The most overlooked advantage of owning a computer is that if they foul up there's no law against whacking them around a bit."

 

[planetjeff]

Hi,
to be more specific, I have (minimally, to lessen confusion) 3 tables. One for Events (with pk EventID), one for Adjudicators (with pk AdjudicatorID), and one for Assignments which tracks a many-to-many relationship between the first two tables (with pk EventID,AdjudicatorID).

I have a form that displays each adjudicator and has two additional subforms - one shows the list of currently assigned events, the other shows a list of all events. I created two buttons, one called AddEvent, the other RemoveEvent. When a user has selected an event in the all events subform, and clicks the AddEvent button, I'd like to grab the EventID, and using the current AdjudicatorID, create a new record in the Assignments table. Conversely, I'd like to do exactly the opposite with the RemoveEvent button. After that, I'm assuming I'd need to requery the queries that comprise the contents of my subforms.

So yes they're values in the form and subform. I don't know how to do this in an SQL stmt. In your example above, what event of the subform would that go? I figured that since I select a row in a subform the values would have to go into variables that are at least global for the main form, and when the button is clicked the values could be used. I am a newbie to Access and have done no VBscripting before, so I don't know at all what the "Docmd.gotoRecord,, AcNewRecord" does. Thanks,
jeff

 

[jedel]

Ok,

I've tried to duplicate your db and I think I have it. You'll need to rejig the fields to suit but have a play with the code below.

This is for the OnClick event to add an Event to an adjudicator to get an assignment. This button is located in your Events Subform:

Dim Evnt, EvId, EvDate, Adj, AdjID As String

Evnt = Me.txtEvent
EvId = Me.txtEventID
Adj = Forms!Adjudicators!txtAdjudicator
AvDate = Me.txtDate

Forms!Adjudicators!sfrmAssignments.SetFocus
DoCmd.GoToRecord , , acNewRec
Forms!Adjudicators!sfrmAssignments!txtEventID = EvId
Forms!Adjudicators!sfrmAssignments!txtAdjudicator = Adj
Forms!Adjudicators!sfrmAssignments!txtDate = AvDate
Forms!Adjudicators!sfrmAssignments!txtEvent = Evnt

This piece is to remove the assigment from the subform. I'm not sure if you wanted to delete the event from the event table either, but you should be able to get there with this example. I created the Delete query using the Query wizard and then just copied and pasted the SQL text from the SQL view. Place this in the OnClick Event on the button in your Assignments subform:

Dim defin As String
defin = "DELETE * FROM Assignments WHERE (((Assignments.AssignmentsID)=[Forms]![Adjudicators]![sfrmAssignments].[Form]![AssignmentsID]));"
DoCmd.RunSQL defin
Me.Refresh
Me.Requery

Hope this helps

Cheers

Dean

[

"The most overlooked advantage of owning a computer is that if they foul up there's no law against whacking them around a bit."

 

[planetjeff]

Thanks to fiddling around with this and your input, I figured it out!

First, I created a public variable in the form:

Option Compare Database
Public selectedEventID As Long

Then, on any particular event subform, if an entry is clicked, I added the following:

Private Sub Form_Click()
    Form_Adjudicators_Assignments.selectedEventID = Me.EventID
End Sub

So when a user selects the "Add Event" button, this is how I add an assignment to the Assignments table:

Private Sub AddEvent_Click()
On Error GoTo Err_AddEvent_Click
    Dim rstAssignment As DAO.Recordset
    Set rstAssignment = CurrentDb.OpenRecordset("Assignments")
    With rstAssignment
        .AddNew
        !EventID = Form_Adjudicators_Assignments.selectedEventID
        !AdjudicatorID = Me.AdjudicatorID
        .Update
    End With
    
    Form_Adjudicators_Assignments_assigned_subform.Requery
    Form_Adjudicators_Assignments_preferredEvents_subform.Requery
    Form_Adjudicators_Assignments_allEvents_subform.Requery
Exit_AddEvent_Click:

And when the user selects "Remove Event":

Private Sub RemoveEvent_Click()
On Error GoTo Err_RemoveEvent_Click
    Dim defin As String
    
    defin = "DELETE * FROM Assignments WHERE ((AdjudicatorID = " & Form_Adjudicators_Assignments.AdjudicatorID & ") AND (EventID = " & Form_Adjudicators_Assignments.selectedEventID & "));"
    DoCmd.RunSQL defin
    
    Form_Adjudicators_Assignments_assigned_subform.Requery
    Form_Adjudicators_Assignments_preferredEvents_subform.Requery
    Form_Adjudicators_Assignments_allEvents_subform.Requery
Exit_RemoveEvent_Click:

Works great! The only annoying thing is the verification message that comes up when deleting the table entry. Is there any way to disable that?

jeff

 

[jedel]

No Probs,

Just before you define the "Defin String" Place this line

Docmd.SetWarnings False

A WORD OF WARNING!!!!

Alway turn it back on again. If you don't, then any errors you get further on down the track, you won't be able to detect.

On the line after Docmd.RunSQL place this line

Docmd.SetWarnings True

Cheers

Dean

"The most overlooked advantage of owning a computer is that if they foul up there's no law against whacking them around a bit."