Solved Form Combo Box Doesn’t Store Selected Value (Foreign Key Issue)

[Anitaa]

I'm building a data entry form for the Peserta_Workshop table in Microsoft Access. This table includes a field called ID_Unit_Kerja, which is a foreign key referencing the Unit_Kerja table. In the form, I'm using a Combo Box to select a unit from Unit_Kerja, and I've set the Row Source to SELECT ID_Unit_Kerja, Nama_Unit_Kerja FROM Unit_Kerja. The Control Source is already set to ID_Unit_Kerja. However, when I select a unit from the combo box, the value does not get saved to the Peserta_Workshop table, and sometimes an SQL or NULL error appears. I’ve set Bound Column to 1 and Column Widths to 0cm;5cm. What could be the issue? Need your help! Thank You!

 

[DHookom]

First, I would check the SQL of the Row Source since this provides an opportunity for your error. Then I would delete the combo box and re-add it.

 

[Anitaa]

First, I would check the SQL of the Row Source since this provides an opportunity for your error. Then I would delete the combo box and re-add it.

If needed, I can share my Microsoft Access file so it can be checked directly. Maybe by looking at the table structure, relationships, and the form setup, it would be easier to identify what’s going wrong. I’m open to anyone willing to help review the file. Thank you in advance

 

[pbaldy]

The error is not on the form, it's in your table. The row source on the lookup tab of that field is invalid. For one thing, there's a stray semi-colon before first FROM. Not many of us use that lookup field feature:

The Access Web - The Evils of Lookup Fields in Tables

Edit: Sorry, second FROM.

 

[DHookom]

The issue is your lookup field (not a good idea) definition in your table design. The Row Source for [ID Unit Kerja] is set to:

SELECT [Unit Kerja Peserta].[ID Unit Kerja], [Unit Kerja Peserta].[Nama Unit Kerja] FROM [Unit Kerja Peserta] ORDER BY [ID Unit Kerja], [Nama Unit Kerja];
FROM [Peserta Workshop]
WHERE [ID Unit Kerja] NOT IN
    (SELECT [ID Unit Kerja] FROM [Unit Kerja Peserta]);
FROM [Peserta Workshop]
WHERE [ID Unit Kerja] NOT IN
    (SELECT [ID Unit Kerja] FROM [Unit Kerja Peserta]);

FROM [Peserta Workshop]
WHERE [ID Unit Kerja] NOT IN
    (SELECT [ID Unit Kerja] FROM [Unit Kerja Peserta]);

There are two semi-colons.

I would recommend getting rid of lookup fields and implement a naming convention that does not allow spaces in object names like tables, fields, and other names.

 

[pbaldy]

By the way, you may not realize you're not seeing the entire SQL string in that row source. If you copy it you'll see:

SELECT [Unit Kerja Peserta].[ID Unit Kerja], [Unit Kerja Peserta].[Nama Unit Kerja] FROM [Unit Kerja Peserta] ORDER BY [ID Unit Kerja], [Nama Unit Kerja];
FROM [Peserta Workshop]
WHERE [ID Unit Kerja] NOT IN
(SELECT [ID Unit Kerja] FROM [Unit Kerja Peserta]);
FROM [Peserta Workshop]
WHERE [ID Unit Kerja] NOT IN
(SELECT [ID Unit Kerja] FROM [Unit Kerja Peserta]);

FROM [Peserta Workshop]
WHERE [ID Unit Kerja] NOT IN
(SELECT [ID Unit Kerja] FROM [Unit Kerja Peserta]);

 

[Anitaa]

By the way, you may not realize you're not seeing the entire SQL string in that row source. If you copy it you'll see:

SELECT [Unit Kerja Peserta].[ID Unit Kerja], [Unit Kerja Peserta].[Nama Unit Kerja] FROM [Unit Kerja Peserta] ORDER BY [ID Unit Kerja], [Nama Unit Kerja];
FROM [Peserta Workshop]
WHERE [ID Unit Kerja] NOT IN
(SELECT [ID Unit Kerja] FROM [Unit Kerja Peserta]);
FROM [Peserta Workshop]
WHERE [ID Unit Kerja] NOT IN
(SELECT [ID Unit Kerja] FROM [Unit Kerja Peserta]);

FROM [Peserta Workshop]
WHERE [ID Unit Kerja] NOT IN
(SELECT [ID Unit Kerja] FROM [Unit Kerja Peserta]);

Thank you very much for your availability to help me. i have done everything directed, related to delete lookud in the properties field and switch to the form. in the form after inputting the code that has been given it still appears like this. how should i do? i am quite frustrated dealing with this access for days.
Any other ideas? I’d really appreciate any further help. I’m happy to share the file if needed. Thank you so much

 

[Anitaa]

The issue is your lookup field (not a good idea) definition in your table design. The Row Source for [ID Unit Kerja] is set to:

SELECT [Unit Kerja Peserta].[ID Unit Kerja], [Unit Kerja Peserta].[Nama Unit Kerja] FROM [Unit Kerja Peserta] ORDER BY [ID Unit Kerja], [Nama Unit Kerja];
FROM [Peserta Workshop]
WHERE [ID Unit Kerja] NOT IN
    (SELECT [ID Unit Kerja] FROM [Unit Kerja Peserta]);
FROM [Peserta Workshop]
WHERE [ID Unit Kerja] NOT IN
    (SELECT [ID Unit Kerja] FROM [Unit Kerja Peserta]);

FROM [Peserta Workshop]
WHERE [ID Unit Kerja] NOT IN
    (SELECT [ID Unit Kerja] FROM [Unit Kerja Peserta]);

There are two semi-colons.

I would recommend getting rid of lookup fields and implement a naming convention that does not allow spaces in object names like tables, fields, and other names.

I have done everything directed, related to delete lookud in the properties field and switch to the form. in the form after inputting the code that has been given it still the same as what i sent above. Any other ideas? I’d really appreciate any further help. I’m happy to share the file if needed. Thank you so much

 

[Anitaa]

Related to this, I became a peer tutor for my undergraduates. Based on the access file that I sent at the top, is there another way to utilize other features without having to use the combo box? Worried that they experience the same thing as me. How is it good? Please give your suggestions about this assignment use the Access, thank you

 

[DHookom]

I deleted the combo box information and changed the Display Control to Text Box. I don't get an error message. If you are still having issues, consider sharing your current file.

 

[DHookom]

Related to this, I became a peer tutor for my undergraduates. Based on the access file that I sent at the top, is there another way to utilize other features without having to use the combo box? Worried that they experience the same thing as me. How is it good? Please give your suggestions about this assignment use the Access, thank you

Combo boxes in forms are awesome. There really isn't a substitute with all the rich functionality.

 

[Anitaa]

Combo boxes in forms are awesome. There really isn't a substitute with all the rich functionality.

Thank you very much for all the directions. Finally I can also use the combo box properly without any more errors, it was constrained by the relationship that used ERI, there should be no need to use it. Have a nice day!

 

[The_Doc_Man]

Just to explain the error message you were getting about something after the semi-colon:

I have edited this query SLIGHTLY to keep things on short lines and to indent things so we can see them more clearly. Look at the lines I marked with <==

SELECT [Unit Kerja Peserta].[ID Unit Kerja], [Unit Kerja Peserta].[Nama Unit Kerja] 
FROM [Unit Kerja Peserta] ORDER BY [ID Unit Kerja], [Nama Unit Kerja]; <==(1)
FROM [Peserta Workshop]
WHERE [ID Unit Kerja] NOT IN
    (SELECT [ID Unit Kerja] 
    FROM [Unit Kerja Peserta]);   <== (2)
    FROM [Peserta Workshop]
    WHERE [ID Unit Kerja] NOT IN
        (SELECT [ID Unit Kerja] 
        FROM [Unit Kerja Peserta]);  <== (3)
        FROM [Peserta Workshop]
        WHERE [ID Unit Kerja] NOT IN
            (SELECT [ID Unit Kerja] FROM [Unit Kerja Peserta]);  <==(4)

On the indicated lines, you have a complete SQL statement that is followed by a semi-colon. Then you have something else the follows the semi-colon. This is an old hack that used to work on SQL processors until folks got wise to this technique. It is called "SQL INJECTION" and is now deemed to be a hack attempt, an attack on the SQL engine.

Marker #1 points to the end of a statement that includes a SELECT, FROM, and ORDER BY clause and ends with a semi-colon. It is self-sufficient so that semi-colon is an SQL statement delimiter. But worse, there is a FROM clause hat follows the semi-colon. That is the first of the SQL INJECTION attacks (i.e. the 2nd FROM is a no-no.) Marker #2 and #3 show similar failings including another SQL INJECTION and a superflous FROM. Marker #4 might actually be legit - if it weren't for the fact that it is in the position of yet another SQL INJECTION.

The SELECT clauses for #2, #3, and #4 ALL involve a FROM [ID Unit Kerja], which is legal, but those queries are actually what we call sub-queries. To have them in a singular statement, you would need to use an ALIAS technique to distinguish the fields in all four queries.

 

[Anitaa]

Just to explain the error message you were getting about something after the semi-colon:

I have edited this query SLIGHTLY to keep things on short lines and to indent things so we can see them more clearly. Look at the lines I marked with <==

SELECT [Unit Kerja Peserta].[ID Unit Kerja], [Unit Kerja Peserta].[Nama Unit Kerja]
FROM [Unit Kerja Peserta] ORDER BY [ID Unit Kerja], [Nama Unit Kerja]; <==(1)
FROM [Peserta Workshop]
WHERE [ID Unit Kerja] NOT IN
    (SELECT [ID Unit Kerja]
    FROM [Unit Kerja Peserta]);   <== (2)
    FROM [Peserta Workshop]
    WHERE [ID Unit Kerja] NOT IN
        (SELECT [ID Unit Kerja]
        FROM [Unit Kerja Peserta]);  <== (3)
        FROM [Peserta Workshop]
        WHERE [ID Unit Kerja] NOT IN
            (SELECT [ID Unit Kerja] FROM [Unit Kerja Peserta]);  <==(4)

On the indicated lines, you have a complete SQL statement that is followed by a semi-colon. Then you have something else the follows the semi-colon. This is an old hack that used to work on SQL processors until folks got wise to this technique. It is called "SQL INJECTION" and is now deemed to be a hack attempt, an attack on the SQL engine.

Marker #1 points to the end of a statement that includes a SELECT, FROM, and ORDER BY clause and ends with a semi-colon. It is self-sufficient so that semi-colon is an SQL statement delimiter. But worse, there is a FROM clause hat follows the semi-colon. That is the first of the SQL INJECTION attacks (i.e. the 2nd FROM is a no-no.) Marker #2 and #3 show similar failings including another SQL INJECTION and a superflous FROM. Marker #4 might actually be legit - if it weren't for the fact that it is in the position of yet another SQL INJECTION.

The SELECT clauses for #2, #3, and #4 ALL involve a FROM [ID Unit Kerja], which is legal, but those queries are actually what we call sub-queries. To have them in a singular statement, you would need to use an ALIAS technique to distinguish the fields in all four queries.

Thank you. It really helpful!

 

[cheekybuddha]

This is an old hack that used to work on SQL processors until folks got wise to this technique. It is called "SQL INJECTION" and is now deemed to be a hack attempt, an attack on the SQL engine.

This is neither a hack, nor SQL injection Doc!

It's just an error in the SQL.

Semi-colon is the [traditional] end of statement character - multiple statements are perfectly valid in RDBMS's that allow them. It's not injection.

Injection is where you pass a poisoned value to an input in a constructed query (eg as a value for a WHERE clause).

Then, using a semi-colon can be a good way to enable you to add your poison.

Obligatory xkcd: Exploits of a Mom

 

[GPGeorge]

This is neither a hack, nor SQL injection Doc!

It's just an error in the SQL.

Semi-colon is the [traditional] end of statement character - multiple statements are perfectly valid in RDBMS's that allow them. It's not injection.

Injection is where you pass a poisoned value to an input in a constructed query (eg as a value for a WHERE clause).

Then, using a semi-colon can be a good way to enable you to add you poison.

Obligatory xkcd: Exploits of a Mom

In a few places, it's true that the terminating semi-colon is optional. Yet, like so many other things I have learned over the years, it's almost always safer to be explicit even when it's not technically required. It takes so little time and effort to terminate a SQL string properly with the ; it seems foolhardy to be cavalier about it.

 

[cheekybuddha]

It takes so little time and effort to terminate a SQL string properly with the ; it seems foolhardy to be cavalier about it.

I'm not sure I ever suggested that you shouldn't use it, George.

 

[GPGeorge]

Just to explain the error message you were getting about something after the semi-colon:

I have edited this query SLIGHTLY to keep things on short lines and to indent things so we can see them more clearly. Look at the lines I marked with <==

SELECT [Unit Kerja Peserta].[ID Unit Kerja], [Unit Kerja Peserta].[Nama Unit Kerja]
FROM [Unit Kerja Peserta] ORDER BY [ID Unit Kerja], [Nama Unit Kerja]; <==(1)
FROM [Peserta Workshop]
WHERE [ID Unit Kerja] NOT IN
    (SELECT [ID Unit Kerja]
    FROM [Unit Kerja Peserta]);   <== (2)
    FROM [Peserta Workshop]
    WHERE [ID Unit Kerja] NOT IN
        (SELECT [ID Unit Kerja]
        FROM [Unit Kerja Peserta]);  <== (3)
        FROM [Peserta Workshop]
        WHERE [ID Unit Kerja] NOT IN
            (SELECT [ID Unit Kerja] FROM [Unit Kerja Peserta]);  <==(4)

On the indicated lines, you have a complete SQL statement that is followed by a semi-colon. Then you have something else the follows the semi-colon. This is an old hack that used to work on SQL processors until folks got wise to this technique. It is called "SQL INJECTION" and is now deemed to be a hack attempt, an attack on the SQL engine.

Marker #1 points to the end of a statement that includes a SELECT, FROM, and ORDER BY clause and ends with a semi-colon. It is self-sufficient so that semi-colon is an SQL statement delimiter. But worse, there is a FROM clause hat follows the semi-colon. That is the first of the SQL INJECTION attacks (i.e. the 2nd FROM is a no-no.) Marker #2 and #3 show similar failings including another SQL INJECTION and a superflous FROM. Marker #4 might actually be legit - if it weren't for the fact that it is in the position of yet another SQL INJECTION.

The SELECT clauses for #2, #3, and #4 ALL involve a FROM [ID Unit Kerja], which is legal, but those queries are actually what we call sub-queries. To have them in a singular statement, you would need to use an ALIAS technique to distinguish the fields in all four queries.

This would not be valid SQL in any database I've worked with, although it's true that I've not seen all that many.

SQL requires at least two clauses:

SELECT XXX
FROM XXX

Additional clauses can be added to filter, group and sort.
WHERE XXX
GROUP BY XXX
SORT BY XXX

But in all cases, valisd SQL must include both the SELECT and FROM clauses.

The SQL in the sample posted is invalid with or without the internal semi-colon delimiters.
Each FROM Clause must follow a SELECT Clause.

Stored Procedures in TSQL can execute multiple SQL statements, and you can call multiple SQL statements in a Passthru query to SQL Server. Other databases support the same method.

Access doesn't.

 

[GPGeorge]

I'm not sure I ever suggested that you shouldn't use it, George.

I apologize for implying that. It's not what I meant; I'm sorry.
I was reinforcing the importance of writing good SQL, and by extension, good VBA. In the context of whether or not valid SQL can be written, say in VBA, with or without the SQL terminator, it can be tempting not to bother to include it. I have been bitten more than once, so I tend to be more verbose, perhaps, than required.

But again, I did not mean to imply anything more than that.

 

[The_Doc_Man]

Injection is where you pass a poisoned value to an input in a constructed query (eg as a value for a WHERE clause).

When I was first taking the U.S. Navy's security courses, having something following the valid end of an SQL statement was described as SQL Injection. Perhaps at the time, multi-statement SQL sequences were less common. If I'm wrong about what to call it, so was the U.S. Navy in the 90's. They DID treat it as a broad-brush designation, however, so perhaps more modern systems now have different names for different facets of this symptom. Nomenclature DOES change over time, and I was dealing with SQL INJECTION issues in the 1990s with an early version of ORACLE.