Help On Form Converter, and a go at Mockers (2 Viewers)

HowardHelp

New member
Local time
Today, 18:54
Joined
Feb 1, 2021
Messages
26
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
 
Last edited:

Pat Hartman

Super Moderator
Staff member
Local time
Today, 14:54
Joined
Feb 19, 2002
Messages
42,971
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

New member
Local time
Today, 18:54
Joined
Feb 1, 2021
Messages
26
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
 

Attachments

  • Query.jpg
    Query.jpg
    43.5 KB · Views: 114

Pat Hartman

Super Moderator
Staff member
Local time
Today, 14:54
Joined
Feb 19, 2002
Messages
42,971
There is no join between the two tables.
Both tables have a repeating group.
Both tables seem to have the same PK.
 

HowardHelp

New member
Local time
Today, 18:54
Joined
Feb 1, 2021
Messages
26
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

Super Moderator
Staff member
Local time
Today, 14:54
Joined
Feb 19, 2002
Messages
42,971
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

New member
Local time
Today, 18:54
Joined
Feb 1, 2021
Messages
26
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

Super Moderator
Staff member
Local time
Today, 14:54
Joined
Feb 19, 2002
Messages
42,971
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

New member
Local time
Today, 18:54
Joined
Feb 1, 2021
Messages
26
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
 

Attachments

  • tabel rel.jpg
    tabel rel.jpg
    44.4 KB · Views: 122
  • tabel rel2.jpg
    tabel rel2.jpg
    32.7 KB · Views: 111
  • tabel rel3.jpg
    tabel rel3.jpg
    36.3 KB · Views: 106
  • tabel rel4.jpg
    tabel rel4.jpg
    71.8 KB · Views: 118

Pat Hartman

Super Moderator
Staff member
Local time
Today, 14:54
Joined
Feb 19, 2002
Messages
42,971
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.
 

Users who are viewing this thread

Top Bottom