Combo box list, related form problem (1 Viewer)

[j.smith1981]

Hello I am new to this forum,

I have a project for a database at my work and having some difficulties getting my head around how to do this.

I have normalised a table and suddenly realised a problem with a form in relation to me doing this.

Ok enough of the waffle, here's the problem:

Ok I have a data sheet (where all records for testing of cartridges are kept and logged).

All the related tables ID columns are existant upon this, but not their true values, for example at the moment I have the following:

data.tech_id which is on the data sheet which has a value of 5, 5 is a primary key number on another table called tech with a field name of id.

So this is a one to many relation, one tech is allowed and they can have many test prints.

But the id is the field thats showing up on the data form, how would it be possible to show another fields value ie what I want instead of having to enter the techs id number in the table, but list a combo box of their name which then looks up their id and puts it into the table (not their name in the data table but their id), but whats visible on the form is their name.

Is this possible some how?

Thanks in advance for any help received, I have never attempted to do this, but having a degree I thought the best thing would be to normalise this data as much as possible.

Thanks again,
Jeremy.

 

[Rabbie]

Base your form on a query that selects the required data from your tables.

 

[j.smith1981]

Base your form on a query that selects the required data from your tables.

I will give that a go thanks so much.

I was feeling rather not confident with VBA in access, I am much more confident with VBA in Excel but I'll just treat it with the same respect back up old copies of the same database.

I am just happy I remembered about table relationships rather than having to go over them and learn them from scratch.

Thanks again,
Jeremy.

 

[j.smith1981]

Ahh slight snag with doing that!

Its not working.

It keeps saying it wont enter the information when I try and save a new record.

Its only inserting the techs name, the person inputting wont remember their id every time (they shouldnt have to bad forms design that idea).

What i was wanting was, say it was me entering an entity in the data sheet.

I would select my 'username' this say equals 11, when i select my name 'jeremy' (this is visible to the user), I then have an id of 11, the ID = 11 then gets inserted into a column in the data table but my username doesnt.

Is this possible please?

Thanks I think this could make it more clear what I require.

Thanks,
Jeremy.

PS You see its not actually adding the record at all, its just displaying the output of that table I think.

Yea its still coming up with the exact same error:

You must enter a value in the 'data.tech_id' field.

Any suggestions someone? I have never had to do this in Access 2007 should be eaiser than doing PHP but I have always been developing for the past 5 years in PHP and MySQL, have no real experience since college with desktop databases, try to avoid them at all costs if preferable.

 

[scooteman]

Do you have One table or two for the info?

Have you tried a subform? I have an employee training from with the employee name and ID in the main form and a subform with training information with IDs as the master and child link field. This way any new records are automatically under the employee ID. I also put a combo box with a recordsearch macro to find my records.

I have an Employee Table and a seperate Training Records Table. The two are related by the employee field and I have referential integrety inforced.

 

[j.smith1981]

Do you have One table or two for the info?

Have you tried a subform? I have an employee training from with the employee name and ID in the main form and a subform with training information with IDs as the master and child link field. This way any new records are automatically under the employee ID. I also put a combo box with a recordsearch macro to find my records.

I have an Employee Table and a seperate Training Records Table. The two are related by the employee field and I have referential integrety inforced.

Technically what I have done is just include the ID as the foreign key with the values on the other table.

Thats how you completely normalise a table, otherwise its just replicating data, data duplication is occuring there, which can cause problems with making an ER diagram for it, its just not the most efficient way of designing tables, I did this from a pure SQL perspective since most of my experience now is from MySQL and Oracle databases.

 

[j.smith1981]

Oh ace!

Sorry I have it solved now, thanks though for your much appreciated help, gave me some ideas though on how to achieve other goals with access to take it a bit further and make it a more usable solution.

Trying to think of something that you'd do in PHP accessing a MySQL database table, but its not relevant for Access thats all, phew I thought I was going crazy then.

Thanks again and kind regards to all,
Jeremy.