Data display question (was Me) (1 Viewer)

[Bungwaa]

Hi Bungwaa

Welcome to the forum

I'm sure that you will find many here that are willing to help. Pleased to hear that your needs will likely be fulfilled in less than 20 years....I don't think I'll live that long

Perhaps you could start a new thread with details of what your db is about and what you need it to do. Include details of your tables.

Hi Bob (Thanks for being here for me for the next 20)

I am using excel (inherited) at work, to keep track of Safety footwear for the workforce, there are currently 9 types allowed and I have to record who has what. the excel is very messy and too many people have access to it (when doing searches in excel some of the users are overtyping data causing the spreadsheet to be almost no use at all.

As I said its 3 tables
CustomerTBL (Includes ID/Auto Number, Clock/Text, Firstname, Surname, Fullname (this is calc from other 2)
CatalogueTBL (Includes ID/Auto Number, Item ID / Number, FootwearCode/Text, Description/text.
TransTBL (Includes transaction details ie Date: Ordered/received/issued, Who the item was issued to/ Comments / What footwear was issued) as below

They are linked by

Then I have this form:

So. . . . . .


When I select the Clock number in the form (Assume the form is for input only (Not editing)) it confirms the Name of the customer and then the rest of the fields are completed. However I want to add a box at the bottom which will auto populate with historic transactions based on the Clock number (Footwear is freely issued if outside 12 months - otherwise checks/reasons required for new issue) the historic transaction shoud show Minimum last 4 entries.

Once again Yes its probably very easy - but each time I hit a snag I take ages trying to resolve with limited success (I want to understand what is going wrong and be able to fix it.

Sorry if its so long winded.

 

[The_Doc_Man]

First and foremost, Bungwaa, welcome to the forums.

Second, our usual practice is when you post a detailed problem description, you pick one of the specific forums and start a new thread. It is also possible to move a post to a new thread. If I can remember how to do that, I will. But I believe you can do it as the original poster (OP).

 

[Bungwaa]

First and foremost, Bungwaa, welcome to the forums.

Second, our usual practice is when you post a detailed problem description, you pick one of the specific forums and start a new thread. It is also possible to move a post to a new thread. I'm going to do that for you and try to leave a link here.

Thank you Doc_Man: for the guidance.

 

[The_Doc_Man]

I'm sure it is possible to move a single post but at the moment I seem to have forgotten the trick.

 

[bob fitz]

Hi Bob (Thanks for being here for me for the next 20)

I am using excel (inherited) at work, to keep track of Safety footwear for the workforce, there are currently 9 types allowed and I have to record who has what. the excel is very messy and too many people have access to it (when doing searches in excel some of the users are overtyping data causing the spreadsheet to be almost no use at all.

As I said its 3 tables
CustomerTBL (Includes ID/Auto Number, Clock/Text, Firstname, Surname, Fullname (this is calc from other 2)
CatalogueTBL (Includes ID/Auto Number, Item ID / Number, FootwearCode/Text, Description/text.
TransTBL (Includes transaction details ie Date: Ordered/received/issued, Who the item was issued to/ Comments / What footwear was issued) as below
View attachment 111908

They are linked by
View attachment 111909

Then I have this form:

View attachment 111910

So. . . . . .


When I select the Clock number in the form (Assume the form is for input only (Not editing)) it confirms the Name of the customer and then the rest of the fields are completed. However I want to add a box at the bottom which will auto populate with historic transactions based on the Clock number (Footwear is freely issued if outside 12 months - otherwise checks/reasons required for new issue) the historic transaction shoud show Minimum last 4 entries.

Once again Yes its probably very easy - but each time I hit a snag I take ages trying to resolve with limited success (I want to understand what is going wrong and be able to fix it.

Sorry if its so long winded.

Please post a copy of the db. Compact and repair it, then zip it and attach the zip file to your next post.

 

[June7]

Does your form have a recordsource that is a query joining tables?

Could have a form/subform arrangement that would allow review of existing transactions as well as new input. Otherwise, a listbox could be used to display existing transactions.

 

[Bungwaa]

Does your form have a recordsource that is a query joining tables?

Could have a form/subform arrangement that would allow review of existing transactions as well as new input. Otherwise, a listbox could be used to display existing transactions.

Sorry June7 - I am not at that sort of standard yet. I dont know.

 

[Bungwaa]

Please post a copy of the db. Compact and repair it, then zip it and attach the zip file to your next post.

As I said its very basic (at the moment) the records in CustomerTBL and CatalogueTBL are genuine the TransTBL only has temp records.

 

[June7]

Answer to my question is Yes. Form has a RecordSource that is a query joining tables. I would not have suggested that as it is an unnecessary complication.

Suggest not having exact same field name in multiple tables. The two ID fields could be Cust_ID and Cat_ID.

I have offered you two options for display existing records. The listbox would require VBA to work, form/subform arrangement would not. If you need a better understanding of form/subform, perhaps this will help https://support.microsoft.com/en-us...any-form-ddf3822f-8aba-49cb-831a-1e74d6f5f06b

 

[bob fitz]

As I said its very basic (at the moment) the records in CustomerTBL and CatalogueTBL are genuine the TransTBL only has temp records.

Take a look at the attached db.
I have used your existing tables to create some new forms that may be more appropriate for your needs.
I notice that you have a "Lookup" field called "What" in the table "TransTLB" which I would advise against. See: http://access.mvps.org/access/lookupfields.htm
Also "ID" is a poor choice of name for an Auto-Number PK particularly if it is used more than once. "CustomerID" or "CustID"
You may find it beneficial to name your Foreign Keys with something more meaningful e.g. "CustFK" rather than "Who"

 

[bob fitz]

After making my last post, I notice that June has already mentioned some of the things that I commented on.

 

[Bungwaa]

Hi Bob
I Tried all sorts of ways to create links (Relations) between the TBLs and NEVER achieved it. that is why I used the Lookups in the tables. however the info and links supplied so far are defo making a difference for me.

 

[bob fitz]

Hi Bob
I Tried all sorts of ways to create links (Relations) between the TBLs and NEVER achieved it. that is why I used the Lookups in the tables. however the info and links supplied so far are defo making a difference for me.

Did you read the link I gave you regarding the use of lookups in tables? You should be using a combo on a form to "Lookup" the PK of required record and then store that PK in your record.

 

[bob fitz]

Bungwaa

In the attached file I have renamed some fields and done away with the Lookups in your table.
Post back with any questions you have.

 

[Bungwaa]

Did you read the link I gave you regarding the use of lookups in tables? You should be using a combo on a form to "Lookup" the PK of required record and then store that PK in your record.

Yes Bob of course I read the article. However I don't know how to do what you suggest "using a combo on a form to "Lookup" the PK of required record and then store that PK in your record." so I will need to try to work it out.

Thanks

 

[bob fitz]

Yes Bob of course I read the article. However I don't know how to do what you suggest "using a combo on a form to "Lookup" the PK of required record and then store that PK in your record." so I will need to try to work it out.

Thanks

Take a look at the file I attached in post #20

 

[Bungwaa]

Bungwaa

In the attached file I have renamed some fields and done away with the Lookups in your table.
Post back with any questions you have.

Hi Bob

Having looked at the file you posted I think its probably beyond the scope of this forum (for me to learn Access) I think I need to find a face to face type course. Don't get me wrong, I appreciate the things you have done - its just the 2 x drop down lists (see image) have got me baffled and so I probably need to be at a higher standard before asking advice in here.

Sorry if I wasted your time.

Best regards

Colin

 

[pbaldy]

Welcome to the site. FYI, I split the technical discussion off to a new thread.

 

[bob fitz]

Hi Bob

Having looked at the file you posted I think its probably beyond the scope of this forum (for me to learn Access) I think I need to find a face to face type course. Don't get me wrong, I appreciate the things you have done - its just the 2 x drop down lists (see image) have got me baffled and so I probably need to be at a higher standard before asking advice in here.

Sorry if I wasted your time.

Best regards

Colin

Hi Colin
First of all, you have done nothing for which you need to apologize.
Now, what you decide to do is, of course, your decision but please keep in mind the following:
There are many members here, ready and willing to help you. Some of them have vast experience in all aspects of Access.
Like me, they make NO CHARGE for their assistance, although I believe the occasional word of thanks is much appreciated .
With regard to the changes that I made to the db, I am more than happy to explain anything that you may have questions about.