Option buttons for entering into joined table

[Lukael]

Hi,

I have a subform of joined table that links table record with 3rd table.

In 3rd table are only 3 text values that need to be stored in record of Main form, so I could replace them with option buttons. Table relationships are many to many, obviously.



So far I tried with setting Controlsource property of Option Button to True, but don't know how to insert ID of 1st table and ID of record with desired Text form 2nd table - into joined table:

Private Sub Option158_AfterUpdate()


If Me.Controls("Option158").ControlSource = True Then
INSERT INTO TblLocation
VALUES(ID of 1st table, ID of record with text String from 2nd table)????
End If


End Sub

Am I somewhere near solution, or do you recommend different approach ?

Thanks for any help in advance !

[ItsMe]

EDIT: I don't know how that thumbs down icon got inserted into my post. I think I hit the tab key on my keyboard by accident and I can't seem to be able to remove it. /EDIT


I don't understand what you are doing with the control source of your Option158 control. The control source is what binds a control to a field/column in a table. Perhaps you want to test the value of the control.

if me.Option158.value = -1 then
msgbox "Value is true"
elseif me.Option158.Value.value = 0 then 
msgbox "Value is false"
else
msgbox "Value is Null"
end if

That aside, you can declare variables and then assign values to your variables. Then you can use your variables in an SQL statement. So something like.

dim lngTableOneID as long
dim strSQL as string

if not isnull(Me.TableOneID.Value) Then
     lngTableOneID = Me.TableOneID.Value 
end if

strSQL = "INSERT INTO TblLocation (TableOneID) VALUES (" & lngTableOneID & ")"

currentdb.execute strSQL

[Lukael]

Thanks for response Itsme.

I've tried your code in Click and After_Update event, but strangely nothing happens. I also tested my value of control, It's -1 when option button is true...

[ItsMe]

Post the code you tried so we do not have to guess. Use the afterupdate event of a control to determine its Value property.

[Lukael]

I tried exact code you provided:

Private Sub Option158_AfterUpdate()
dim lngTableOneID as long
dim strSQL as string

if not isnull(Me.ProgrammeID.Value) Then
     lngTableOneID = Me.ProgrammeID.Value 
end if

strSQL = "INSERT INTO ProgrammeLocation (ProgrammeID) VALUES (" & lngTableOneID & ")"

currentdb.execute strSQL

End sub

Value property - should I include that in code too, with If structure ?

P.S.: post#1 was wrong, I need to insert ID numbers in joined table, that's ProgrammeLocation, not tblLocation...

[Lukael]

I changed this line to (notice single quotes):

strSQL = " 'INSERT INTO ProgrammeLocation (ProgrammeID) VALUES (" & lngTableOneID & ")' "

SQL statement now exectues, but I get error :

"RUN-TIME ERROR '3078': The Microsoft Access database engine cannot find the input table or query 'name'. Make sure it exists and that its name is spelled correctly.

And I have checked spelling of tables and ID field names, they are correct ?

[Micron]

It's -1 when option button is true...

0 is always false; true can be -1 or 1 depending on the specifications as it was designed to be.
It's not clear that you understand that when dealing with option buttons, it is the frame (group) whose value you must deal with, not the option button itself. Once you get the sql sorted out, this may present another problem that you might not have dealt with yet. Also, I think your single quotes are not nested properly and you should have the semicolon at the end as well:

strSQL = "INSERT INTO APOLokacija (Sifra_APO) VALUES ('" & lngTableOneID & "');"

I am not positive because the variable name you created suggests lngTableOneID is a long integer but a table name (which is what it looks like it's referencing) would be text. If so, remove the single quotes because the way I wrote it is for a text variable.

[Lukael]

Hi Micron,

sorry to say but your code also doesn't do nothing. ID fields of both tables are long integer. About error 3078, this is what I get exactly:

RUN-TIME ERROR '3078': The Microsoft Access database engine cannot find the input table or query "INSERT INTO ProgrammeLocation (ProgrammeID) VALUES(3)". Make sure it exists and that its name is spelled correctly.

Value 3 that you see in error description is ID number of table that needs to be inserted in field ProgrammeID (table ProgrammeLocation) and is correct value.

My joined table ProgrammeID is number field, maybe that is problem ? But also long integer, and I cannot create relationships many-to-many with 2 Autonumber ID's ?!?

[Lukael]

I have made sample file, so you can see on your own what's happening, and what I want to do. Just click Option button on form.

I've also tried with

DoCmd.RunSQL "INSERT INTO ProgrammeLocation (ProgrammeID) VALUES (" & lngTableOneID & ")"

instead of CurrentDb.Execute. Try It yourself and you'll see what happens, looks like there is Number field problem...

P.S.: So far this is only one part of SQL statement, I need to insert into joined table value from 3rd table too - you'll see in sample.

Please any advice !

[ItsMe]

...dealing with option buttons, it is the frame (group) whose value you must deal with, not the option button itself....

The option button control does not have to be associated with an option group. If it is a standalone, it is a tri-state control (0, Null, or -1).

[ItsMe]

@Lukael

I took a look at your DB. The code in my original post will do the job (there is the exception of the name Option158 and a typo where I included .Value twice).

The reason it was not working for you is that you have set the Primary Key constraint to your columns within table ProgrammeLocation. These columns are not primary keys, they are Foreign Keys. Using the DB you uploaded here, I was able to get the code to work by doing two things.

1. Remove Primary Key property from columns within table ProgrammeLocation.
2. Remove single quotes from VBA SQL statement.

This will get the code to execute, but it does not seem likely this is your desired effect. If you look at the code I provided, there are two examples. The first provides an If, Then, ElseIf statement that will "Control the Flow of Statements". You need to employ this to get a more desirable result. As you code is now, it will append a record each time the value of your option button's value is updated.

There are other issues as well ...

[Micron]

The option button control does not have to be associated with an option group.

You are correct; didn't know it was alone until I looked at the form.
Lukael: I already wrote that you must not use quotes around a number, and your table data type is long so remove the quotes.
strSQL = "INSERT INTO ProgrammeLocation (ProgrammeID) VALUES (lngTableOneID);"
Even then it will not work because you are trying to insert one value in one field where the table requires two - you have created a composite primary key in ProgrammeLocation. You will need something more like
strSQL = "INSERT INTO ProgrammeLocation(ProgID,LocationID) VALUES (1,2);" where 1 and 2 will be your variables.

[Micron]

He probably wants a composite index on those two fields instead of composite key.

[Lukael]

ItsMe,

thanks for providing this answer, code really works, but looks like it's not performing quite well - If you allready have table ProgrammeLocation opened in design mode you don't see any update, value is seen after you re-open table..But that's no problem for me....

I am more concern with this - what issues does bring If joined tables aren't set with primary keys ? In every tutorial I've seen that's recommended structure when you want to link in many-to-many relationship. And they are set as numbers, not Autonumbers, so why doesn't SQL just insert that value into field ?

[ItsMe]

...In every tutorial I've seen that's recommended structure when you want to link in many-to-many relationship....

For me, I prefer to use code to manage most constraints and referential integrity. If you follow the tutorials and use the relationships window to define relationships, you can use bound controls to quickly build forms to perform CRUD operations. For me, defining relations in the Relationships Window is too difficult. It becomes difficult for me when I need to provide a user with an interface that is flexible.

There may be a way to integrate both code and the relationships window. I have never really tried to do this. I just use code. Either way, you will need to break off little pieces of your app and isolate each piece as an abstract idea. From there, use your knowledge of VBA and Access to decide what tools to use. In other words, discover the hurdles when you try to put the pieces back together in a way the User is happy and willing to use your application.

Consider Micron's point that you need to add both key values to the table in the same instance. Is this flexible enough for your Business Rules? In some instances it may be OK. In other instances it may not be so much fun. Also, it may be OK for certain entities and not others. Maybe it is OK for a new customer order but when you get to working with inventory you discover you need more flexibility.

Your example does not encompass all of the hurdles you will discover. You need to explore. Perhaps researching the difference between a composite key and a compound key will help. If all you need to do is create a junction table to manage a many to many, I would avoid defining primary keys in the junction table.