Update and Create New Records

M

Mercadogs

Hello:

I'm working on a database that has a table linked to about 16 other tables
in a one-to-many relationship. What is the best way to update and create new
records in my situation?

If been trying to create a form for this, but the wizard crashes before
creating it.

The answer of experts would help, especially in the simplest language, for I
am novice! Thank you in advance.
 
C

Charles Calvert

I'm working on a database that has a table linked to about 16 other tables
in a one-to-many relationship. What is the best way to update and create new
records in my situation?
Click to expand...

That's a vague question. It's difficult to answer with anything other
than "create a form and put some controls on it." If you have a more
specific question, someone may be able to provide more specific
answers.
 
J

John W. Vinson

Hello:

I'm working on a database that has a table linked to about 16 other tables
in a one-to-many relationship. What is the best way to update and create new
records in my situation?

If been trying to create a form for this, but the wizard crashes before
creating it.

The answer of experts would help, especially in the simplest language, for I
am novice! Thank you in advance.
Click to expand...

Sixteen related tables is a LOT - sort of a "sea urchin" relationship design.
Just out of curiosity, what *are* all these tables!?

The approach I'd take is to make a Form based on the "one" side table, with 16
subforms each based on one of the child tables. You will almost surely want to
put a Tab Control on the mainform and put one or a couple of subforms on each
page of the tab control, just to keep the form from getting too crowded.

John W. Vinson [MVP]
 
M

Mercadogs

Thanks a lot John. I will try your approach.

These 16+ tables are all two-field tables that contain the actual text of the
one side table along with a (automnumber) primary key. So the only data the
"one" side table have are the foreign keys of the 16+ tables and it own
(autonumber) primary key. This is to reduce memory space used by repetitious
data in a (non-Access) "one" side table.

This project is an item clarification database with a large number of fields
with info related to each item.
 
J

John W. Vinson

Thanks a lot John. I will try your approach.

These 16+ tables are all two-field tables that contain the actual text of the
one side table along with a (automnumber) primary key. So the only data the
"one" side table have are the foreign keys of the 16+ tables and it own
(autonumber) primary key. This is to reduce memory space used by repetitious
data in a (non-Access) "one" side table.

This project is an item clarification database with a large number of fields
Click to expand...

I'm sorry, this isn't making sense to me. The 16 tables "contain the actual
text of the one side table" and an autonumber field as well???

What direction are the relationships? You're not storing text data
redundantly, are you?

John W. Vinson [MVP]
 
M

Mercadogs

The "text data" is stored only once in a table. That data is assigned an
autonumber. that autonumber shows up many times as a foreign key in the "one"
side table.

This is true for the other tables as well.

Does it make sense now?
 
J

John W. Vinson

The "text data" is stored only once in a table. That data is assigned an
autonumber. that autonumber shows up many times as a foreign key in the "one"
side table.

This is true for the other tables as well.
Click to expand...

Thanks. In that case you don't need any subforms AT ALL. Just put Combo Boxes
on the main form bound to the foreign key fields; the combo box wizard will
set this up for you so that the computer sees the numeric ID and the user sees
the looked-up text from the other table.

John W. Vinson [MVP]
 
M

Mercadogs

Thanks John:

I got this to work. The text shows up, however, now the entries are bound to
the values on the list. How can I change this?

In other words, I want the user to also be given entry suggestions as they
type, like a regular combo box. When I try to Choose "No" for the "limit to
list" option, Access denies me this. The message reads:

"The first visible column, which is determined by the Columnwidths property,
isn't equal to the bound column. Adjust the ColumnWidths property first, and
then set the LimitToList property."

When I do this, then I the combo box displays the number not the "text data" .

I'll keep on trying. Thanks again for all your help!!
 
J

John W. Vinson

Thanks John:

I got this to work. The text shows up, however, now the entries are bound to
the values on the list. How can I change this?

In other words, I want the user to also be given entry suggestions as they
type, like a regular combo box. When I try to Choose "No" for the "limit to
list" option, Access denies me this. The message reads:

"The first visible column, which is determined by the Columnwidths property,
isn't equal to the bound column. Adjust the ColumnWidths property first, and
then set the LimitToList property."

When I do this, then I the combo box displays the number not the "text data" .

I'll keep on trying. Thanks again for all your help!!
Click to expand...

Well, do you want to limit to the list, or do you want the user to be able to
type in anything they want (in addition to the offered values)? You can't have
both!

Try setting the properties of the combo to:

RowSource - a query based on the lookup table, sorting the text alphabetically
Control Source - the ID field in your main table
Column Count - 2
ColumnWidths - 0;<some nonzero width to display the text>
Bound Column - 1

You can use some VBA code in the combo's Not In List event if you want the
user to be able to type in a new value and have that value added to the lookup
table; go to www.mvps.org/access and search for NotInList for sample code.
Just a warning: this will (not may, WILL) let users put garbage -
misspellings, near-duplicates, etc. - into the lookup table.

John W. Vinson [MVP]
 
M

Mercadogs

I do not want to limit user the list box. I was under the impression that a
combo box should allow the user to input new information or choose from the
list. Is this not so?

On your previous message, when you said to set property " RowSource - a
query based on the lookup table, sorting the text alphabetically" what did
you mean by "a query based on the lookup table"? Do I have to create a query
of some sort?
 
J

John W. Vinson

I do not want to limit user the list box. I was under the impression that a
combo box should allow the user to input new information or choose from the
list. Is this not so?
Click to expand...

It's so only in certain cases. You can allow the user to type data freely,
putting in a value which is not in the combo box, ONLY if the combo box is
storing the actual text value. If you have a lookup table with a numeric ID
(concealed) and a visible text field, you must leave Limit to List set to Yes
(because the user has no direct way to create a new number value).

If you do use Limit to List = No and a single field combo box, bear in mind
that the value that the user types will not automatically be added to the list
for future selection. What you may want to do is to leave Limit to List = Yes
and use the combo's "Not In List" event, with VBA code to add the user's new
entry to the lookup table. This is a bit more work up front for you but may be
the best approach.
On your previous message, when you said to set property " RowSource - a
query based on the lookup table, sorting the text alphabetically" what did
you mean by "a query based on the lookup table"? Do I have to create a query
of some sort?
Click to expand...

Yes. If the combo box is based directly on the table, it will present the
entries in *numerical* order based on the numeric primary key. This is not
likely to be all that useful for the user! You don't need a separate stored
query; instead you can have the RowSource of each combo set to a SQL string
like

SELECT LookupID, LookupText FROM LookupTable ORDER BY LookupText;

to present the data in alphabetical order.

John W. Vinson [MVP]
 
M

Mercadogs

Hi John:

Maybe this will be my last question.

I've tryed the SQL code and it seems promising, but...

I got Run-time error '3078', and then Run-time error '91'.

I'm sure it's just that I'm confused about what items to change in the
following SQL you gave me the link to. Can you Highlight the code section I
need to change. Please! Thank you again for all your help.
..................................................................................................................

Private Sub cbxAEName_NotInList(NewData As String, Response As Integer)
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim strMsg As String

strMsg = "'" & NewData & "' is not an available AE Name " & vbCrLf &
vbCrLf
strMsg = strMsg & "Do you want to associate the new Name to the current
DLSAF?"
strMsg = strMsg & vbCrLf & vbCrLf & "Click Yes to link or No to re-type
it."

If MsgBox(strMsg, vbQuestion + vbYesNo, "Add new name?") = vbNo Then
Response = acDataErrContinue
Else
Set db = CurrentDb
Set rs = db.OpenRecordset("tblAE", dbOpenDynaset)
On Error Resume Next
rs.AddNew
rs!AEName = NewData
rs.Update

If Err Then
MsgBox "An error occurred. Please try again."
Response = acDataErrContinue
Else
Response = acDataErrAdded
End If

End If

rs.Close
Set rs = Nothing
Set db = Nothing
End Sub
.............................................................................
 
C

Charles Calvert

Maybe this will be my last question.

I've tryed the SQL code and it seems promising, but...

I got Run-time error '3078', and then Run-time error '91'.
Click to expand...

If you open the table, can you manually insert a new value?
 
M

Mercadogs

Hi Charles:
The answer is yes. However, I got my issue resolved already. Thanks for your
post!
 
C

Charles Calvert

The answer is yes. However, I got my issue resolved already. Thanks for your
post!
Click to expand...

You're welcome. Can you post the answer to your question so that
people who search through the archives on the same problem will find
your answer? That would be helpful.
 
Top