Base - adding data to table with 2 primary keys

[cr4y]

Hi,

I’m working on small database describing when particular people has done particular things.

I have table Dates that contains UserID, EventID and Date. First two are primary key.

I’m looking for convenient way of putting data into this table. I’ve created form with two list fields and one data field, and it makes adding data easy, BUT if there already is record for particular UserID-EventID combination, I need to find it manually using arrows and then edit. I hoped that setting UserID and EventID as primary key will require me to only choose these two fields correctly from list and then new record will be added OR old one will be updated, but I was wrong.

I think that it’s quite common task, but I cannot find any solution. I’m not sure if I should learn writing macros, or maybe there is easier way of doing it using sql or something. Any help will be appreciated, thanks in advance,

Kris

[frofa]

Kris:

Your table (as above) seems to be a junction table, and it looks like your main focus is on UPDATING the Date column value for an existing record. So therefore you need to be able to efficiently ‘locate’ a particular record (for update) in terms of its UserID and EventID, yes?

I think it would be more efficient if you use form-based filtering techniques rather than trying to do it is the table listing window - see this tutorial on the AOO forum for a fuller explanation of form-based filtering techniques. Essentially, your table (grid) list is in the sub-form, and your filters (either boxes or drop-downs as you like) are in the main (parent) form.

How it should work: You would simply type-in the UserID in the main-form filter field (which functions like a search box), and the list in the sub-form will then only show records for that UserID (for editing the Date value or the row you choose manually). Or, alternatively, you should be able to set your form up so that you have TWO search boxes, one for UserID and one for EventID, so that you only would get a single row for editing in the sub-form list. See the tutorial above for detailed examples of how to do it.

One final point. Although you seem to be using a COMPOUND PRIMARY KEY on the two columns UserID and EventID so that each row of your table is uniquely identified, I’m not clear why you need to do this (actually I didn’t realize it was even possible until now)? Instead, it might be ‘safer’ and simpler if you had a single additional integer auto-value/auto-increment column (called, say UsEvID) to do the job, and the UserID and EventID columns then would just function as foreign key columns linked to the two other User and Event tables I assume exist in your database. I found this answer which might be relevant.