Option buttons for entering into joined table

[Lukael]

Ok, I think I understand what you're saying. I'm not quite fammiliar with Access, but I have some experience with VBA in Excel.



Actually all I need is to add records in junction table that matches Programme record with Location, in order to have an option in future for running queries based on junction table, such as listing locations to see what programmes are allready located there.

So basically If I'm not wrong, this will do the job same as junction table with primary keys ?

Micron, thanks for info about inserting into second row of ProgrammeLocation, and YES I need that too. For option button in sample file second INSERT value will be 1, as this Option button is for Location New York, which is listed as LocationID record no "1". As I told in post#1, this solution is only for better end-user experience of entering data in form, because table Location has only 3 values, and will not be entered any more. So to simplify adding records of Location for Programmes, I will just add 3 Option buttons with labels of location name and assign them different INSERT values for second value in SQL statement. Problem was only first value, since It represents record Autonumber - which changes.

Thanks a lot for help guys !!

[ItsMe]

Ok, I think I understand what you're saying...

Unfortunately, there is not any easy way to explain it and there is not an easy way to learn it, either.

When you do not have a broad understanding, it makes it more difficult to ask questions and it is more difficult to focus on building the correct parts of your database/application. As I mentioned, you need to break off little chunks and apply Business Rules.

Consider using default values in the properties of fields for your tables. While in design view of your table, determine if a default value is helpful. I almost always provide a default value for Numbers and Booleans (Yes/No).

Understand when and how to define a Primary Key and how this is different from a Foreign Key. Understand that you cannot duplicate values for Primary Keys. You can duplicate values for Foreign Keys. While in design view of your table, look at the index properties of your fields and understand when a duplicate value should be allowed. Not every column should be indexed. PK's and FK's should be indexed.

[Lukael]

O.K.,

let us shorten this thread to finish It....Will I or WILL NOT have any problems with runnning a junction table query (with your provided code), such as bounding Combo to list Programme items for each location ?

About business rules...There are none at the moment, because application will be stand-alone for archiving purposes. But in future there might be needs for displaying different kind of reports or even communicating with Oracle database, so beside "good-looking" solution I still need solid database design, for avoiding my hard times over next years at job

[ItsMe]

Yes, you will have problems. You need to append one record. With the current setup, there is nothing preventing the user from clicking the option button 50 times. There is nothing to prevent the user from appending 50 records.

What I do is collect user input via controls. Sometimes, there is a need to use unbound controls. Sometimes I use bound controls.When I need to append to a junction table, I will declare variables and assign values to the variables. With the variable, I can do data validation. After data validation, I will use the variable in an Action query like an append query or an update query (There are other ways too, but a query will work just fine.).

I will try to do as much as I can in a single shot. I will take one trip to the data to append a record(s). As the user interacts with the form, the variables may change. When the user is done, they use a Command Button to execute the append/update. Using a command button is different than using an option control to append a record. Option controls, TextBox controls, ComboBox controls, etc. are for user input. Command buttons are for executing code that performs CRUD operations.

[Lukael]

I have tested now and everything works, queries based on junction table too. I also added in code for deleting records, so now option buttons work as I need them to:

Private Sub Option6_AfterUpdate()

Dim lngTableOneID As Long
Dim strSQL As String

If Me.Option6.Value = -1 Then

   If Not IsNull(Me.ProgrammeID.Value) Then
      lngTableOneID = Me.ProgrammeID.Value
   End If

   strSQL = "INSERT INTO ProgrammeLocation (ProgrammeID, LocationID) VALUES (" & lngTableOneID & ",1)"
   CurrentDb.Execute strSQL
End If

If Me.Option6.Value = 0 Then
   
   If Not IsNull(Me.ProgrammeID.Value) Then
       lngTableOneID = Me.ProgrammeID.Value
   End If

   strSQL = "DELETE FROM ProgrammeLocation WHERE(ProgrammeID)=(" & lngTableOneID & ")"
   CurrentDb.Execute strSQL
End If

End Sub

Thanks for help to both of you, specially Itsme for providing code, informations and advices. Nice conversation overall

[ItsMe]

That looks like it will work. Let us know if you want additional help. It is common to refactor code after first building and testing it.

[Micron]

Here's another approach that may pique your interest about the Select Case method. You'll notice it's more concise (albeit only by a few rows because you only had two IF blocks); partly because of the Select block, partly because the sql variable is set to an empty string to begin with, then tested later. Without that test, it would try to execute if the value was neither -1 or 0. Sometimes I find it's necessary to code as Case Is = n instead when referring to controls.

Private Sub Option6_AfterUpdate()

Dim lngTableOneID As Long
Dim strSQL As String

strSQL = ""
Select Case Me.Option Value
    Case -1
    If Not IsNull(Me.ProgrammeID.Value) Then lngTableOneID = Me.ProgrammeID.Value
    strSQL = "INSERT INTO ProgrammeLocation (ProgrammeID, LocationID) VALUES (" & lngTableOneID & ",1)"
    Case 0
     If Not IsNull(Me.ProgrammeID.Value) Then lngTableOneID = Me.ProgrammeID.Value
    strSQL = "DELETE FROM ProgrammeLocation WHERE(ProgrammeID)=(" & lngTableOneID & ")"
End Select
If strSQL="" then Exit Sub
CurrentDb.Execute strSQL
End Sub