Help On Form Converter, and a go at Mockers

[HowardHelp]

You only need to use a combo if you want to update the field by choosing from the list. If you simply want to display the text value as you would in a report, use a query that joins the main table to the lookup table (use a left join if the lookup field is optional). Then select the text value from the lookup table and bind it to a textbox to display. Make sure to LOCK the text box because you don't want anyone to accidentally update the lookup value.

Are that sound's better, what do i need to include the the AUID and the filed from the small table and the field from the main tabel, and then link the field to the forms unbound box

I just joined the field in the control source and its cumming up #Name? i don't think the query is right

 

[Pat Hartman]

It's hard to guess without the query.

Select tbl1.fld1, tbl1.fld2, tbl2. fldA
From tbl1 Left Join tbl2 ON tbl1.SomeID = tbl2.SomeID

Then you would be able to bind fldA to a textbox. Don't forget to LOCK it!!!!

 

[HowardHelp]

It's hard to guess without the query.

Select tbl1.fld1, tbl1.fld2, tbl2. fldA
From tbl1 Left Join tbl2 ON tbl1.SomeID = tbl2.SomeID

Then you would be able to bind fldA to a textbox. Don't forget to LOCK it!!!!

Here is the query I just done the tables are not linked but the fields are below, look-up table is on the left

 

[Pat Hartman]

There is no join between the two tables.
Both tables have a repeating group.
Both tables seem to have the same PK.

 

[HowardHelp]

There is no join between the two tables.
Both tables have a repeating group.
Both tables seem to have the same PK.

It looks like i will have to stick to the combo box, I've tried or sorts of things none worked.
The combo works because of the row source and the code SELECT [snCat1].[AUID], [snCat1].[fldMusicType] FROM [snCat1] ORDER BY [AUID]; the text box is different obviously.

 

[Pat Hartman]

Both tables having the same primary key is confusing. I'm going to assume that this is simply a naming issue and that the tables are not related 1-1.

If that is the case, then the problem is that you are storing the text value of music type rather than the primary key. The structure is completely wrong. We need to help you fix that before you move on.

 

[HowardHelp]

Both tables having the same primary key is confusing. I'm going to assume that this is simply a naming issue and that the tables are not related 1-1.

If that is the case, then the problem is that you are storing the text value of music type rather than the primary key. The structure is completely wrong. We need to help you fix that before you move on.

Is it possible for me to email you the cut down version of the database, as the tables are how they were designed, I don't really want to post the database on the site its under 1mb

 

[Pat Hartman]

If you don't want to post the database, post a picture of the relationship window. Make sure that we can see all the tables. Cut it into several pictures if you need to.

 

[HowardHelp]

If you don't want to post the database, post a picture of the relationship window. Make sure that we can see all the tables. Cut it into several pictures if you need to.

OK the other way would of been simpler but here’s some of the important pics

 

[Pat Hartman]

1. Using AUID for the pk name for every table just obfuscates the relationships. Standard is to use a meaningful PK name and then use the same name or the same name with a suffix of _FK as the foreign key.
2. Always create relationships in the relationship window and enforce Referential Integrity. Don't use cascade update. It isn't used when your PKs are autonumbers anyway since autonumbers cannot be changed so there is never an update to cascade. Cascade delete is delicate. Do not use it if you don't understand what it does. Use it if you understand when it should be used.
3. prefixing all names with fld is a waste of space and requires you to type an extra three characters each time you reference a field.
4. Lookups defined at the table level are a crutch for newbees and cause more problems than they solve. Best to remove them and just use the standard technique of a query with a join. Use combos on forms to pick from the list. You get the same functionality but avoid the issues. Access automatically creates combos on forms/reports when you have defined the field as a lookup.
5. You have several repeating groups. These are easy to identify when you use numeric suffixes to indicate a field that occurs multiple times. These belong as separate tables. Do some reading on normalization.