Primary key and entering data (1 Viewer)

[Sa7ar]

Hello
I'm still a newbie in the Access world and I need your help
Here is the situation:
I'm using Microsoft Access 2007
I have three tables: the 1st is for product's identification, the 2nd is for registered products, and the 3rd is for under-registration products
and the primary key for the three tables is the Registration Number
and there is a one to one relationship between the product identification and the registered products
and a one to one relationship between the product identification and the under registration products
what I want to do is to make an append query to move the under registration product to the registered product when its process is over
One of my problems is with the primary key for the under registration products table, as they only get their registration number when the process is over. so how can I enter data into this table without the value of the primary key ??

Please help me with this issue
and If there is anything that isn't done right so far..

 

[Swemebegur]

Hi Sa7ar

It seems to me that you should have only one product table with a field indicating the status - under registration, registered. That is unless the the data in each of the tables is very different, when it might make sense to have one-to-one sub-entities. Can you show us the structure of your database?

Swemebegur

 

[khodr]

Hello sa7ar
As Swemebegur you can have one table indicating the status of the product
Then you need to create two queries the first query is registered_product_identification_query and the second one under_registration query then create a form showing the first query data then create another form which showing the second query data for under registration products
Now go back to the registered products form and create a button and set the on click event to open the under registration products form.
Conclusion:
A) you have a form to show you the registered products
B) you have in the same form a button which allows you accessing the second part of the table for the under-registration products and update to registered
Little long but easy feel free to post your question in case this is not enough
Regards

 

[Sa7ar]

Thank you so much Swemebegur & Khodr you're life savers
I will go for the one table for all products and a status field. Fields for registered and under registration products are slightly different and it would be ok to leave some fields as NA.

Two more questions please:
1) can you please tell me how to make a form that shows a certain query result ?
2) now that I can't use the registration number as the primary key for this one table (as the under registration products won't have their registration number until the registration process is over), should I make ID field as auto number or leave the table without a primary key ?

 

[Aleni]

Bound your form to query(control source)
As per me its better to have primary key in a table.

 

[Sa7ar]

Thanks Aleni
Can you please give me steps for how to bound my form to a query ?

 

[Aleni]

open your form in design view-in the property sheet of your form you will see record source-there you select your query to which you want to bound your form.
I hope this is what you are asking.

 

[Sa7ar]

Thank you Aleni
I made the query and now in the record source I have this statement > SELECT AllProducts_Table.Status FROM AllProducts_Table WHERE (((AllProducts_Table.Status)="Under-Registration"));

But now all the fields say > #Name?
and in the design view of this form it shows this > Invalid Control Property: Control Source / No such field in the field list

What did I do wrong ?

 

[khodr]

Sa7ar,
if you can put your tables, queries, form in a sample database and upload it here so maybe we can connect it together for you and upload it back,
but before that you said you don't know how to connect queries to your form, after you click the button in the form properties to link your query to the form you should get a windows showing you the table and you will see a grid in the botton of the window like a datasheet, in the table small window up there you will find a star, double click it then it will show the table name on the datasheet, then click save and close the window, then your control in the form should work if it doesn't click on every combo and make sure its holding the proper name in the data source on the right side properties panel.
regards.

 

[Sa7ar]

I'm sorry for the late reply but the database couldn't be attached here (and I don't know why..)

 

[Sa7ar]

I have uploaded a sample of my database, here is the link:
It is on mediafire

http://www.mediafire.com/download.php?hmpxlkkom4l6i4n

 

[Sa7ar]

I'm hoping you could help me

 

[khodr]

Hello Sa7ar,
I have downloaded your database, the size was about 55 MB that is why maybe you couldn't attach it here, but there were no data inside, so I applied the compact and repair procedure to the database and the size went down to 469 KB lots of un-needed size, i believe you need to apply a compact and repair to your current database, the compact and repair you will find it on the Toolbar Ribbon, refer to Database Tools and you will see Compact and Repair Database on the maximum Right Top. click it and Access will do everything, always backup your database before applying any procedures in Access I will fill some data in your database for sampling and try to work out what you want.
I will come back to you soon,

 

[Sa7ar]

Thanks for the tip about compact and repair option
I didn't enter any data into the database because I thought that would make the size of the database smaller but I guess it didn't work . sorry about that
I know there is still alot for me to learn.. thanks for your help

I think bounding the form to a query didn't work because I typed "under-registration" in the criteria for the status field. but I should have typed "2" , right?

 

[khodr]

well,
I am working now on your database you would have the updated one shortly, I hope you like it actually it needs some design reforming to be user friendly I will attach it back to you in here you can download it and keep it separate from your real database until you are sure what is happening on it and sure I will explain your questions.
you are most welcome.

 

[khodr]

Dear Sa7ar,
Here you got the database, I have changed your form Design a bit as I said to look more user friendly, what I want from you before you copy this forms to your database, kindly check it carefully and try to understand carefully how it works,
a) I added four command buttons for your filtering purpose,
b) You will find three tabs in the form, one is holding the Product List, another is Product Details where you enter your product data and the third one is the Date and Comments Tab.
c) With your Product list you can see your products list as per the product ID.
d) When you click on sort registered then you will only see registered products on the list
e) If you click on any item on the list your products details will be refreshed to show you that item.
f) You cannot add any new record unless you click sort all to turn off filtering. When you click on the new record you will get a message telling you what you have to do.
g) I added a close button to your form.
h) On the date and comments field you date fields now accept only date and when you click on the filed you will see the date picker beside it for easier date selection.
Try to understand all these how they are made, click on the buttons and check their event read the code and try to understand it and if you have any question feel free to ask.
I don’t know what is exactly your user entry behavior but if you had unexpected output just feel free again to ask.
Khodr

 

[Sa7ar]

really appreciate your help
but the database attached won't open >>
Unrecognized database format "F:\project DB\copy of DB project 16 dec 12.accdb"

 

[khodr]

what is the version of access you are using? the database is designed to work with Access 2010 I download it back and checked it and it works fine, tell me then what version of access you are using by then i have to design the database to work with that version.

 

[Sa7ar]

well .. I have access 2007
that is why it couldn't open a 2010 access database

 

[khodr]

2007 hmm ok let me see what I can do I will check and come back to you
In all cases don't worry in the morning when I go to the office I will convert the database and upload it again