Adding new records to an automated form

[Glen]

I don't think this is a very hard problem but I just don't have enough
experience with Access to solve this. I have a form (frmUser_Data)
which has just 7 fields. The primary key is a ship hull number which
will populate the rest of the fields if it is matched from the table.
I have code in the AfterUpdate event of the combo box ShipHull which
accomplishes this just fine. My problem is that I want to be able to
input new ship hull numbers and all information associated with them in
the appropriate fields and then update the existing table. I also
don't want to alter any data that is already in the table, ex: when I
type a new value in the Ship Hull field for a lookup. I have been
screwing around with the NotInList event but it conflicts with my
AfterUpdate procedure. I appreciate any and all suggestions.

 

[Roger Carlson]

Your principle problem is that you have not designed your tables properly.
It sounds to me as if you are storing hull data (ship data?) in the
User_Data table, copying all of those field from another table. In fact,
you should not copy all that data from one table to another. Instead, you
should create a relationship between those table and store ONLY the primary
key (hull number) in the User_Data table. When you need to pull this data
together, you can then Join them in a query.

--
--Roger Carlson
MS Access MVP
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L

 

[Glen]

Thank you Roger but you are mistaken. I actually have 4 Tables. This
User_Data table contains 7 fields which basically pertain to ship
details, such as vessel name, class, build dates, etc., with the
primary key being the Hull Number. This Hull Number will relate this
table to the other three. As I am sure you know, there's no reason for
me to store the ship information with 80,000 material records or even
the several hundred records of ACAD drawings associated with each ship.
I can work just fine with setting the relationships in tables and
queries, I am just not that familiar with getting the forms to work as
desired.

The form is going to be used to populate a header on an automated
spreadsheet in excel. If there are already hull numbers in this table,
I don't want people to have to enter all seven fields in the form.
That will present the problems associated with typos and data entry
errors. I would like for the form to be populated with all of the
fields if the hull number exists and protect these fields form being
overwritten or modified throgh the form. At the same time, I would
like to be able to enter new records to this table if the Hull Number
does not exist in the table.

Thanks again for your response and I would welcome any other
suggestions for this problem.

 

[Roger Carlson]

Sorry for misunderstanding.

I usually do something like this with a form/subform, where the mainform has
the main table as it's record source and the subform has the child table of
the 1:M relationship as it's record source. I'll use a combo box to find
the record I want, but the rest of the controls on the mainform will be
locked. In the NonInList event, I'll have code that either unlocks the
controls or pops-up a data entry form for adding a new record.

On my website (www.rogersaccesslibrary.com), is a small Access database
sample called "NotInList.mdb" which illustrates how to do this.

--
--Roger Carlson
MS Access MVP
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L

 

[Glen]

Thank you Roger.

I will check out the samples and see if I can get something working for
me from it.

 

[Glen]

Alright Roger. Thank you very much for your samples. It was not too
difficult to rearrange my form to work. I think I had one fundamental
error with the way I was setting the form up. I was trying to use the
combo boxes to hold the information and filter it at the same time. I
think the problem with my thinking is that there are several records
that can have a hull number and several others that can have a ship
class. I was failing to set the focus on the index of the matched
records - [ID_Code] from your example. I ended up setting one combo
box that combines the two fields for the user as a lookup. That data
is then placed into text boxes for further operation as needed. I have
to say I learned a lot from checking out your code. Thank you for your
help.