autonumber with a difference

[Guest]

hi
i am trying to create a form with some specifics. here goes:
I need to have a form in a datasheet view so i can enter in timesheets. what
i need with it is, in the header, i want a dropdown list with vessel names
and then a field with a logsheet number. i would like the vessel which is
selected in the drop down list, to automatically appear in another 'vessel'
column in the datasheet for each entry of that logsheet. i then want that
logsheet number to be unique for each logsheet only, not each entry, that is
why i want to place it in the header BUT i also want it to show next to each
entry so when i look up a specific entry, i can see which logsheet number it
relates to.

so, basically, (and i realise i may have to adjust my tables a lot too) i
need a form which will have a different logsheet number each time it is
opened in 'add mode'. i need it to all fall into a table somewhere with all
the timesheet information so i have to creat relationships too. i dont
suppose anyone will understand any of this.

if you do, please help. thank you
Rigby

 

[Roger Carlson]

If I'm reading this correctly, it sounds like you want a form/subform setup.
This would involve two tables in a One-to-Many relationship. The LogSheet
table would have one row for each sheet. The LogSheetDetails table will
have the details for each logsheet. In the LogSheetDetails table, you store
the LogSheetID (primary key of LogSheet) as a Foreign Key.

You base the parent form on LogSheet and the subform on LogSheetDetails and
link the two in the subform control Parent/Child properties. The subform
control will take care of entering that for you in the LogSheetDetails
table.

On my website (www.rogersaccesslibrary.com), is a small Access database
sample called "FormSubform.mdb" which illustrates this.

--
--Roger Carlson
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L

 

[Guest]

Roger
It sounds like you know what I am trying to do. unfortunately, there are
some of your suggestions which i don't really understand. Firstly, I dont
know how to set a foreign key. my logsheet table has 2 feilds. one is called
'logsheet ref no' which i had as an autonumber but was causing more
difficulty for me, so i have set it for now as just a number. its also my
primary key. then, i have another field called 'vessels' which i have as a
combo box which is using a SQL statement to look up values in another table.
now, my problem with this part is, if there are duplicate values in the other
table ('status') where the values are being looked up, it shows it in the
combo box, which i dont want, i just want it to show each value once. I could
enter each vessel name manually each time, but there are possibly hundreds of
these, it would be simpler if my lookup query worked but didnt show duplicate
values.
Is this possible??

regarding the subform, i tried that in my details section of my main form,
and i almost had it working. when i clicked on new record of the main form,
it would then change my 'logsheet ref no' automatically but then not in the
subform. i was hoping that by creating a relationship between them, the
subform would be updated automatically. my main form has 2 fields, the two
mentioned in the 'logsheet' table.

my subform has these fields: 'logsheet no' (i want automatically updated as
the main form is entered), 'vessel' (which i want updated as a choice is made
from the same field in the main form and carried onto each record for that
logsheet number), 'date', 'ref no' (which i dont want as it is only there as
a primary key, no need for it at all otherwise), 'port', 'Employee ID',
'Employee Name' (which i need to create a lookup for too), and then a few
more irrelevant fields.

does this help??
Thank you so much. I am still looking through your samples and your website.
Rigby

 

[Roger Carlson]

Let's do this in stages:
Duplicates in Combo:
This one is easy, just add the word DISTINCT after the SELECT in your combo
SQL statement, ie SELECT DISTINCT Vessels FROM Status

Setting Foreign Key:
This is what the subform control was created to do. In order to make it
work, go to the Properties of the subform Control. In Access 2000 and
above, it can be a little difficult to find. I usually right-click on the
very, very top of the subform control and choose properties. You'll know if
you got it right, because you'll find Link Parent and Link Child properties.
Here is where you put your fields that create the relationships: [logsheet
ref no] for the parent and [logsheet no] for the child.

Once this is set up properly, every time you change records in the man form,
you will only see related records in the subform. Also, when you add a new
record to the subform, the foreign key will be populated with the value of
the primary key of the main form.

On my website (www.rogersaccesslibrary.com), there is a Tutorials section
which has several lessons on creating Applications. You can find them here:
(http://www.rogersaccesslibrary.com/TutorialsApplication.html). All of the
3rd lessons have instructions for creating a form/subform. (It would
probably be easier to follow if you did the previous 2 lessons, though.)

Lastly, I don't see any reason to duplicate the Vessel in the subform. If
the vessel name applies to every record in the subform, this properly
belongs in the parent form and table.


--
--Roger Carlson
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L

 

[Guest]

Dear Roger
You are an amzing help. Thank you so much for all your time and help. I
understand about having the vessel field not repeated in the subform, and i
agree, it's not necessary now. i created the parent/child link with the
'logsheet ref no' and it worked perfectly. unfortunately, i am still having a
problem with the vessel list giving duplicate values in the combo box. i used
the wording DISTINCT, yet it still gives duplicate values. i think i may know
where the problem lies, but i am not sure how to overcome it.

when i use the lookup wizard in creating the combo box for 'vessels', once
it has finished it shows up like this in the rowsource:
SELECT [Status].[ID No], [Status].[Vessels] FROM [Staus] ORDER BY [Vessels].

I am worried that its pulled up the 'ID No' field without giving me any
option to do so, and when i delete that part of the statement, it wont show
anything in the combo box at all. how can i avoid this? therefore, it seems
to be referring to each ID No's individual listing of their vessel. is there
a way to work around this problem?

Thank you
Rigby

 

[Guest]

Hi

I have another quick question regarding my relationships. these confuse me
utterly. Regarding my 2 tables: 'Logsheet No' and 'Logsheet Details'. Now I
have created a 'many-to-one' realtionship from 'logsheet details' to
'logsheet No' with the link from the field 'Logsheet No' in the 'Logsheet
Details' table to the field 'Logsheet Ref No' in the 'Logsheet No' table
where there are just the 2 feilds (the other being the vessels list). is this
the right kind of relationship?

Thank you
Rigby

 

[Roger Carlson]

Well, the Wizard does like to do that.

Ideally, you would pull this value from a Vessels table where each vessel
had only one entry. Then the ID business would work. However, if you still
want to pull it from Status, remove the ID field from the query (as you
did), then change the BoundColumn property of the combo to 1 and ColumnCount
to 1 and delete anything in the ColumnWidth property (or change it to 1").

--
--Roger Carlson
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L

 

[Roger Carlson]

Let me explain it a little more generally. When creating a One-to-Many
relationship, you take the Primary Key (PK) of the One-side table and put
that in the Many-side table as a Foreign Key (FK) and create your
relationship based on that. I recommend that the PK be an Autonumber field
and the FK be a Long Integer. Graphically, it looks like this (hope the
formatting comes out OK):

Table1 Table2
------- --------
Table1ID(pk)--| Table2ID(pk)
Field1 |--< Table1ID(fk)
....Other ...Other fields

So if I understand your setup correctly, it would look like this:

LogSheetNo LogSheetDetails
------------- -----------------
LogSheetRefNo(pk)--| RefNo(pk)
Vessels |--< LogSheetNo(fk)
Date...etc.

--
--Roger Carlson
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L

 

[Guest]

Roger

Again, you have been such a help. I am convincing my boss to donate to your
website for all the much needed help you have provided. Thank you. I have
been able to sort out that SELECT DISTINCT query now with your help. I have
also changed my tables around a bit so i now have a separate table for my
vessels list. i am busy putting it through now and i think it should make a
big difference. i dont know if you have seen, but i have sent you another
question which i have pasted below:

Hi

I have another quick question regarding my relationships. these confuse me
utterly. Regarding my 2 tables: 'Logsheet No' and 'Logsheet Details'. Now I
have created a 'many-to-one' realtionship from 'logsheet details' to
'logsheet No' with the link from the field 'Logsheet No' in the 'Logsheet
Details' table to the field 'Logsheet Ref No' in the 'Logsheet No' table
where there are just the 2 feilds (the other being the vessels list). is this
the right kind of relationship?

Thank you
Rigby

Otherwise, i will now spend some time looking through your tutorials. Thank
you!
Rigby

 

[Roger Carlson]

I answered that further down in the thread, but perhaps you missed it. To
repeat:

Let me explain it a little more generally. When creating a One-to-Many
relationship, you take the Primary Key (PK) of the One-side table and put
that in the Many-side table as a Foreign Key (FK) and create your
relationship based on that. I recommend that the PK be an Autonumber field
and the FK be a Long Integer. Graphically, it looks like this (hope the
formatting comes out OK):

Table1 Table2
------- --------
Table1ID(pk)--| Table2ID(pk)
Field1 |--< Table1ID(fk)
....Other ...Other fields

So if I understand your setup correctly, it would look like this:

LogSheetNo LogSheetDetails
------------- -----------------
LogSheetRefNo(pk)--| RefNo(pk)
Vessels |--< LogSheetNo(fk)
Date...etc.

--
--Roger Carlson
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L