Transmittal Database

[civram1982]

Hi everyone Im creating a database that will automate my day to day making of transmittal this transmittal is made in excel and I want to make in access so that i can minimize the redundant entry.



Hope that someone will help me or give me some ideas.

Thank you!

[CJ_London]

you'll need to be much clearer on what you are trying to do. Explain the business and the process. Also, what is a transmittal? What are the redundant entries?

Access is not a bigger excel, you will probably need multiple tables and defined relationships

[civram1982]

Transmittal its like a cover sheet of a document for example I submit 10 drawings with different dwg no, dwg title with the same revision. Its like a list of all document that I will submit.
The redundant entries is the reference number every time I have a new submission i will type it. I will attached a sample I already make some tables.

Thank you

[CJ_London]

Your current setup will cause issues because ReferenceNumber in tblTrans allows duplicates - so your tblDwg records 'won't know which record to connect to'

You need to modify tblDwg to remove the reference number field and create a new field called say SNFK and link it to the SN (the PK field) in tblTrans then you can just drag tblDrawing onto your form.

Note it is a bad idea to have spaces in field names - use the caption property if you need spaces, that is what it is for

[civram1982]

Ajax thank you for your reply I think I got what your point is but I have some question please check the picture below.

[CJ_London]

don't understand what you are asking - autonumbers cannot be reset. if you want that sort of thing you will need to use something like what is commonly called 'dmax+1' - google to find out more

[civram1982]

Thank you.. what Im trying to do is that TR-001 have 5 drawings its one record in table trans and if I make another record like TR-002 SN should reset to number 1 so that the counting of drawing will start to 1. Every new record should start to 1 in table drawings.
I try to remove the auto number but it will take time because of manual entry.

[CJ_London]

your explanation is not making sense to me, please provide some example data. You seem to be saying you have multiple tables with the same structure which is a big no no

[civram1982]

sorry about my explanation. Please check my sample file.

Thank you

[CJ_London]

sample file does not contain tables called TR1 and TR2. You understand what you are looking at, others don't without a clear explanation. All I can see is you have not followed my advice from earlier posts

[civram1982]

Thank you.. can you show me some example.. TR1 and TR2 is the data I entered and the data in the drawing table is also manually entered.

[CJ_London]

can you show me some example

I really do not understand what you are trying to achieve if Dmax+1 is not the solution. Have you googled it? have you tried it? As one of thousands of examples, see this link - in particular look at post #6

https://access-programmers.co.uk/for...d.php?t=246828

[civram1982]

Ajax,

Thank you for your reply.. I found the solution about the dmax+1. This is the formula that I used put that code in the afterupdate of a combobox.

   
If Me.NewRecord = True Then
        Me.[CounterNo] = DCount("*", "tblDrawing", "DwgID<=" & DwgID & " AND TransID=" & TransID) + 1
End If

I have question in revision. For example I'd like to resubmit Reference TR-01 with the same details except the revision no and submitted date also the drawing no will have the same details except for the revision no. Attach is my sample database

thank you again

[CJ_London]

For example I'd like to resubmit Reference TR-01 with the same details except the revision no and submitted date also the drawing no will have the same details except for the revision no. Attach is my sample database

appreciate there is a language issue but I do not understand what you require. provide example data and explain the process, referencing that example data and what the final result is required from that example data. you refer to TR-01 but that is not in your data and not a field or table name. You know your business, I don't

[civram1982]

Sorry about my English and explanation. TR-01 is a Reference No. stored in tblTransmittal the field name is RefNo
Here is the process of the database Im working.