• ** There has been a recent site upgrade. Please clear your browser cache to avoid issues. **
  • New forum feature - post voting and best solution

    Check out this thread for the details: https://www.access-programmers.co.uk/forums/threads/new-forum-feature-post-voting-and-best-answer.314134/

    This new feature looks great to me! :)

  • We now have 3 forum themes

    Go for the default (light) theme, Shades of Grey or Shades of Blue. I just added the Blue one.

    The thread about it is here: https://www.access-programmers.co.uk/forums/threads/new-forum-theme-shades-of-blue.314136/

Subform isnt adding first field to table (1 Viewer)

CallumMascall

New member
Local time
Today, 04:18
Joined
Sep 18, 2020
Messages
12
Hello, i am absolutely brand new to access and no basically nothing at all
I was supposed to do a unit on databases for my extended diploma in IT but i never got to learn anything because the virus shut my college down

Anyway, i am making a database system for the company i work for (agricultural company). On this database will be an order tracking system for parts we have ordered where i have a table for orders (tblOrders) with a unique order number. Then i have an order details table (tblOrderDetails) that will have the different parts ordered.

I have a 1 to many relationship set up so that the unique order number from tblOrders is assigned to each field from that order in tblOrderDetails

I have a subform set up in my main form so that you can enter multiple different parts and then when you submit it, you get a single order in tblOrders and all the parts from that order in tblOrderDetails. It kind of works, it adds them, but for some reason the order number is not being put in the very first field, after that every entry has the related order number.

Im fully aware ive jumped in the deep end.

Attached is screenshots of my form and the tblOrderDetail. As you can see the order number is "order" but is not there for part number 1234 (ItemID 11) yet it is forpart number 5678 ( ItemID 12)

Im very confused, very new to access and aware this is probably not explained very well, im not very good with all the technical words yet! Sorry!
 

Attachments

  • Order Form.PNG
    Order Form.PNG
    31 KB · Views: 12
  • order details table.PNG
    order details table.PNG
    13.4 KB · Views: 11

namliam

The Mailman - AWF VIP
Local time
Today, 05:18
Joined
Aug 11, 2003
Messages
11,663
Can it be that the record still is in "edit mode" on the form, which is why it looks wrong in the table?
 

CallumMascall

New member
Local time
Today, 04:18
Joined
Sep 18, 2020
Messages
12
Can it be that the record still is in "edit mode" on the form, which is why it looks wrong in the table?
I dont think so, ive made sure it isnt but it still does the same thing, i have a whole plethora of issues to be honest with you, im just trying to attack them one at a time

Unless by edit mode you mean it still looks like a datasheet? in which case i will be sad because then i wont know how to add multiple orders at once XD
 

Gasman

Enthusiastic Amateur
Local time
Today, 04:18
Joined
Sep 21, 2011
Messages
6,764
I do not understand how you can have it in one record and not the other, unless you changed something in between records.?
 

CallumMascall

New member
Local time
Today, 04:18
Joined
Sep 18, 2020
Messages
12
I do not understand how you can have it in one record and not the other, unless you changed something in between records.?
i havent changed a thing in between. both of those records came from the same order submission
 

CallumMascall

New member
Local time
Today, 04:18
Joined
Sep 18, 2020
Messages
12
I do not understand how you can have it in one record and not the other, unless you changed something in between records.?
Here is another example, the two parts are under the order number "ORDER"

the orders table recognizes the order number as "ORDER" the order details table also does, but only for that second part (Part 5678)
 

Attachments

  • Order Form.PNG
    Order Form.PNG
    23 KB · Views: 10
  • order details table.PNG
    order details table.PNG
    15.2 KB · Views: 9
  • Order Table.PNG
    Order Table.PNG
    15 KB · Views: 10

namliam

The Mailman - AWF VIP
Local time
Today, 05:18
Joined
Aug 11, 2003
Messages
11,663
Using ORDER or your ORDERNR as a Primary and/or Foreign key is a big issue to start with. Meaningfull keys is a big NO NO.
Suggest you change your keys to work on meaningless Autonumbers.

There is probably some variable being used in the wrong order?!
Can you upload the db?
 

Gasman

Enthusiastic Amateur
Local time
Today, 04:18
Joined
Sep 21, 2011
Messages
6,764
As namliam states, use the AutoNumber for the 'real' linking and just populate your record with the correct value, if you need to see it for some reason.?
I would have perhaps thought ORDER would be a reserved word as well? :unsure:

Have a look at the Northwind example.
 

CallumMascall

New member
Local time
Today, 04:18
Joined
Sep 18, 2020
Messages
12
Using ORDER or your ORDERNR as a Primary and/or Foreign key is a big issue to start with. Meaningfull keys is a big NO NO.
Suggest you change your keys to work on meaningless Autonumbers.

There is probably some variable being used in the wrong order?!
Can you upload the db?
yeah i can, its nowhere near finished or operational yet, im just trying to get the fundamentals working and will probably make the seasoned professionals cringe big time because its a bit of a mess at the moment but here it is

I wouldn't be opposed to using a pre-made database and slightly changing it to our needs but i doubt i could find one of those very easily
 

Attachments

  • Lite-Trac Order Tracking.accdb
    600 KB · Views: 8

CallumMascall

New member
Local time
Today, 04:18
Joined
Sep 18, 2020
Messages
12
As namliam states, use the AutoNumber for the 'real' linking and just populate your record with the correct value, if you need to see it for some reason.?
I would have perhaps thought ORDER would be a reserved word as well? :unsure:

Have a look at the Northwind example.
Perhaps, i am very new to access though so all of these things are all foreign to me, i know what autonumbers are and primary keys etc but i basically need things explained in steps. i know im hard work, sorry😕
 

namliam

The Mailman - AWF VIP
Local time
Today, 05:18
Joined
Aug 11, 2003
Messages
11,663
Duh, its your on timer event, it adds the record before the ordernr is entered....
WHY WHY on earth would you do such a timer? Net price is a calculated value, we dont store calculated values in proper database designs.
 

CallumMascall

New member
Local time
Today, 04:18
Joined
Sep 18, 2020
Messages
12
Duh, its your on timer event, it adds the record before the ordernr is entered....
WHY WHY on earth would you do such a timer? Net price is a calculated value, we dont store calculated values in proper database designs.
i have absolutely no idea because im so new to this :/ ive just been trying different things. That timer was to get the net price to update automatically based on the quantity and unit price because i couldnt figure out another way

Like i say, im very new to this, are you able to explain an effective way to achieve what im trying to do?
 

Gasman

Enthusiastic Amateur
Local time
Today, 04:18
Joined
Sep 21, 2011
Messages
6,764
Perhaps, i am very new to access though so all of these things are all foreign to me, i know what autonumbers are and primary keys etc but i basically need things explained in steps. i know im hard work, sorry😕
Everyone starts somewhere, but do a little study first.
You would not try and swim the Channel if you had only just learnt to swim. :)

Have a look at the Northwind Example.

This is a 2007 version, but the concept is the same. Also it does use macroes :(
 

Attachments

  • Northwind 2007.zip
    3.8 MB · Views: 8

CallumMascall

New member
Local time
Today, 04:18
Joined
Sep 18, 2020
Messages
12
Everyone starts somewhere, but do a little study first.
You would not try and swim the Channel if you had only just learnt to swim. :)

Have a look at the Northwind Example.

This is a 2007 version, but the concept is the same. Also it does use macroes :(
Thank you, i'll have a look, its just difficult being one of the only IT orientated people in the business, these kinds of jobs get put on me :/ no doubt i'll be back with more silly questions in the future
 

namliam

The Mailman - AWF VIP
Local time
Today, 05:18
Joined
Aug 11, 2003
Messages
11,663
Perhaps, i am very new to access though so all of these things are all foreign to me, i know what autonumbers are and primary keys etc but i basically need things explained in steps. i know im hard work, sorry😕
[rant]
Sorry but if you dont know what to do with autonumbers and primary keys... what are you doing making a database? Sorry to be harsch but go back to your excel sheet or word document.[/rant]

Sigh.
What you do is when ever you make a table, ie. tblOrders is make an ID field, ie. OrderID... this is always the same like in the naming convention for your tblOrders and tblOrderDetails, you would have OrderDetailsID for example and SuppliersID
Note on your naming convention: Table names are more commonly singular, meaning tblOrder, tblSupplier, tblOrderDetail etc....

Once you have the ID column you make it AUTONUMBER and make it your Primary key.
I.e. once OrderID is your Primary key for tblOrder, now you make it your foreign key for your tblOrderDetail instead of your Order Number
While on the Order Number, having spaces in your column names is a bad habit and should not be used to avoid future troubles. Fix it now before your go anywhere. Yes spaces belong on labels in forms and reports, spaces do not belong inside your db.
While on the subject neither do special characters like -, *, #, $, % etc nor reserved words like Order, Select, From, Form and what not else reserved words there are.
Why not use Order Number as your PK? Order number means something in your business area, things that mean something have a tendancy to change at some point. Rule #1 of a primary key, It shall never ever change.
Hence use a meaningless autonumber PK, hide it from the user but use it inside your database.

While ranting on naming conventions, you do have tblOrder but where is your prefix on your forms? Why have "Order form" (again no spaces here).
frmOrder
sfrmOrderDetail
etc.
 

Gasman

Enthusiastic Amateur
Local time
Today, 04:18
Joined
Sep 21, 2011
Messages
6,764
Thank you, i'll have a look, its just difficult being one of the only IT orientated people in the business, these kinds of jobs get put on me :/ no doubt i'll be back with more silly questions in the future
Been there, done that. :D

Consider getting a book. My previous employers bought me 'Access 2007 for Dummies' :)
 

CallumMascall

New member
Local time
Today, 04:18
Joined
Sep 18, 2020
Messages
12
[rant]
Sorry but if you dont know what to do with autonumbers and primary keys... what are you doing making a database? Sorry to be harsch but go back to your excel sheet or word document.[/rant]

Sigh.
What you do is when ever you make a table, ie. tblOrders is make an ID field, ie. OrderID... this is always the same like in the naming convention for your tblOrders and tblOrderDetails, you would have OrderDetailsID for example and SuppliersID
Note on your naming convention: Table names are more commonly singular, meaning tblOrder, tblSupplier, tblOrderDetail etc....

Once you have the ID column you make it AUTONUMBER and make it your Primary key.
I.e. once OrderID is your Primary key for tblOrder, now you make it your foreign key for your tblOrderDetail instead of your Order Number
While on the Order Number, having spaces in your column names is a bad habit and should not be used to avoid future troubles. Fix it now before your go anywhere. Yes spaces belong on labels in forms and reports, spaces do not belong inside your db.
While on the subject neither do special characters like -, *, #, $, % etc nor reserved words like Order, Select, From, Form and what not else reserved words there are.
Why not use Order Number as your PK? Order number means something in your business area, things that mean something have a tendancy to change at some point. Rule #1 of a primary key, It shall never ever change.
Hence use a meaningless autonumber PK, hide it from the user but use it inside your database.

While ranting on naming conventions, you do have tblOrder but where is your prefix on your forms? Why have "Order form" (again no spaces here).
frmOrder
sfrmOrderDetail
etc.
Thank you very much for the tips. It's nowhere near done, i have a lot to learn but i appreciate your time and efforts. Sorry again to be a pain
 

namliam

The Mailman - AWF VIP
Local time
Today, 05:18
Joined
Aug 11, 2003
Messages
11,663
Also it does use macroes :(
In case you dont get it, that means... on average we dont use macro's ...

We were all inexperienced at some point, there is no "pain" in asking questions.
Knowing you have a lot to learn is already a lot better over many that think they are all that.
 

Users who are viewing this thread

Top Bottom