Using a query View with a List Box to find a “MemberID” by searching the drop-down list. The source table for the query is the same table being used in the search process.
When trying to run this, I receive "Error updating current record: Violation of unique constraint SYS_PK_47: Duplicate value for column “MemberID” in statement UPDATE “MemberMaster” SET “MemberID” = ? WHERE “MemberID” = ?
Error updating SQL Status 23000 Error Code 104
As a newbie, I’m lost to find this problem. Can I get some direction
SAS Member Master.odb
I see you have a problem but as to what is causing the problem is unclear. The reason is you don’t explain what is on the form or the process you’ve undergone to get the error.
Not even sure you need any query. It seems you want a list box containing data of a certain field in a table - say name. Using that selected name, you want to bring up that record on the form for whatever reason. Is this the case?
The goal is to use a list box to show members’ names, then select their record. I used a query to combine first and last name; “LastName” || ', ’ || “FirstName” AS “Name” , “MemberID” then created a View. Using the View as the source for “Name” and “MemberID” made a dropdown. The dropdown works, but when using Tab to select the choice, the error occurs. The selected “Name” also sometimes changes when pressing the Tab key??
Dear Ratslinger,
Thank you very much for your detailed reply and downloadable. Unfortunately, I’m such a novice that I couldn’t see anything I could actually look at and make sense of. Sorry.
Joe
I understand your situation. This is just the basics of Base. Would you be willing to post a copy of your .odb file so I may help? I would not want something with personal or confidential information.
If that is not plausible, and in case you are not aware, here is the site for the Base documentation which may be of help to you - click here.
I’m happy to share my .odb if I can figure out how to post it. Neither Member Master or Payments forms work. Thank you for your help. Also, I’ve downloaded your sample Customer Selection.odb several times, but it does not download as an .odb, but as a compressed (zip) file with a lot of stuff.
@JoeCastor That would help. I have increased your karma to allow you attachments. Edit your original question and using the paperclip icon on the toolbar (upper left of question) attach you .odb file. Also, I am not sure what is happening on your side with my sample. It downloads fine for me. What OS are you using? The downloaded file SB a number.odb What is the name of the downloaded file you get?
Update: I was able to get your file to open. I just ignored the .zip and opened it as an .odb file. I’ll begin to study it.
Thank you again.
Duplicate value for column
means that MemberID is defined as requiring unique items, in other words no two records can have the same value for MemberID. It’s called something like a ‘unique field constraint’. Read on…
UPDATE "MemberMaster" SET "MemberID" = ? WHERE "MemberID" = ?
looks like the SQL that is trying to update MemberID and failing (when you open the pull down box and select an item).
To fix this either don’t put the same thing in more than one record (via the pull down selector), or remove the constraint in your table design (the “unique” constraint) to allow multiple records to have the same value.
I am just trying to select a current record from the table. It seems that the a new record is trying to be created. The Key is not an auto generated key, but assigned when created. So, is these a setting or command that would say GET the record and not CREATE a new one?
The separate query and View are not necessary. What you are attempting is simple filtering.
The list box gets its information from the table in a manner acceptable to you, like a built in query, such as last, first name along with the table key for each record. The actual list box is set to update a Filtering table. This is a one record table to temporarily hold a selected value.
On a sub-form, the data is retrieved from the table you want by way of a SQL command. This will retrieve the record wanted by using the selected information placed in the temporary filter record.
It is not as difficult as it may sound. I have attached a sample for you to see how this works. Pay attention to the properties of the List box and the sub-form. Those are key. In the list box, there are two fields being selected: Field 0 is the combined name; Field 1 is the record key. Field 1 is used in the Data properties of the list box under Bound field. So the list box displays Field 0 but saves the data of Field 1 in the Filter table field. The Button is necessary for refreshing the sub-form information (look at the Action property setting). This can also be accomplished without a button but requires macro coding. As you state you are new to this, basic coding is probably way beyond your reach right now.
Finally, first & last name may not always be enough for a unique selection. Additional concatenation of data is possible.
Sample: CustomerSelection.odb
Edit 6/19/2017:
I have attached your original file which includes a “Filter” table and a new form “PaymentsSelectByName”. The problem with all your forms is the same. If you are going to use a list box for record selection, it can’t be on the same internal form as the actual detail. It is basically the same concept as presented in my answer above - List box on the form and detail of selected information on a subform to the form. If you have difficulties with this concept, I suggest you go over the documentation I pointed to in my comment - specifically Chapter 4 - Forms.
The Query and table View are not necessary for any of this and should be removed.
Returned Sample - SAS Member Master.odb
Edit 6/19/2017:
So I took a few minutes to look at you master form. Got some problems there. If this is just to view records, not much - just filter problem. But if you are looking to modify or add records - A few problems. In this sample I modified the “MemberMaster” form. It now has the list box in ascending alpha (last name) seq. The button on sub form titled “Select Record” to display the record in the list box. Another button on the Main form titled “Refresh List” which after adding a NEW record will refresh the list in the List Box. To enter a NEW record, select New Record icon on navigation bar and the fields will be blank except for the ID (Badge) field. This is because you did not set the ID as an Integer & to auto increment. The new record will still add if you enter an unused ID.
Another Sample: Corrected SAS Member Master (copy).odb
If this answers your question please click on the 
(upper left area of answer).
@JoeCastor Please note in this latest change to master form, I used linking for connection to get the record and not SQL. Look at subform on properties data tab.
I just realized - you did have the ID field on the form. It was listed as “Badge”. You can delete my added field. Sorry for the confusion. I changed the sample.
Thank you very much. Your new forms works great.
Is it possible to insert an instruction to present the names in alpha order (ascending)?
Fro your earlier model, I was able to get the search to work, but the connection to the sub-form key isn’t working, but I’m sure the answer is in the actual form you sent.
Thanks again.
Joe
Oh, one final question? Can a (same) table be used for both the Main and the sub-form on the same Form?
Thanks again in advance.
Change the file extension from .zip to .odb.
Change the file extension from .zip to .odb.
Just change the extension from .zip to .odb and it should work fine. In case you were unaware, LO files are really multiple zipped files. Typically Base has quite a number of files within an odb and even the data files if using the Embedded DB.
Sequence - Edit form. Right click list box. Select Control. On Data tab, select ellipsis (...) right edge of List content property. Under the field to be sorted, click on the sort box then select ascending or descending. Then save.
Each Form or subform has one data source. In actuality you can have 5 main forms or subforms or subsubforms (etc.) all looking at the same table all on the same Base form.
Please remember to click the checkmark if this has now answered your question.