Creating form for data entry

[justme1234]

Hello,
I am fairly new to Access and not sure how to proceed. Here is a description of my database:

I am trying to create a database to track software licenses installed on servers. For each installation, I need to take a license request token, install it on a server to generate a license request key and then take that key to a web site to get a license key and finally install the license key back on the server; that license can only be used on that server and will not work on any other server.

Each license token can be used a total of 3 times, again, each token should only be installed on one server at a time. If the server die or is no longer needed on that particular server, the license can be moved or software uninstalled and the license goes back to the pool.

So here is the database I made:

1. Server table: ServerID, ServerSN, ServerPartNumber, PurchaseOrderNumber, Location. The ServerID is an auto generated number and is the primary key.
2. License table: LicenseID, LicenseRequestToken. The LicenseID is an auto generated number and is the primary key.
3. Installation table: InstallationID, ServerID, LicenseID, InstallationDate, LicenseRequestKey, Licenses. The InstallationID is an auto generated number and is the primary key. The ServerID and LicenseID are linked to the ServerID and LicenseID of the Server table and License table.

I think this design ok and I tested it with just the table and it seems to work fine. But I would like to create a form to allow others to enter data. Basically a form to pair up the server and license and then enter all the other info in the Installation table. I tried using the Form wizard based off the Installation table and it showed the correct data but it would not allow me to add new record. So far the only way for me to add new record is to go to the table and manually type in the ServerID and LicenseID.

I would like to do 2 things:

1. Create a form that has a drop down boxes showing all servers without software installation and all licenses not assigned to server and allow the user to pair the data and enter all other info such as installation date, etc.

2. Put that same data in a history table (I have not create that yet) to track where the license has been previously installed.



I am not sure how to proceed with these 2 tasks and any help/suggestion would be really appreciated. Thanks.

Denny

[aytee111]

If you created a form using the wizard based off the Installation table then there is no reason why it does not allow you to enter new records. There must be some other reason, like maybe the form is based on a query instead? There is also a property called AllowAdditions which may be set to No, altho the wizard would not have set it like that.

First, create a query which shows all licenses currently in use. Note that there will be NO history table, the installation table shows all history and you don't want to repeat any data. So this query must ignore servers that are no longer in service and must take the maximum date showing the latest server. Include in this query a count of the number of times a license has been used - this would be a separate query joined in. Once you have this query working, you can use it to list servers without a license and licenses that are free to be use

Make sure that the form is based on installation table only, not a query. Have a combobox or listbox for the user to select a server based on the above query. Another combo/listbox showing all licenses that are open. These will be bound fields and the user enters all the other data.

[justme1234]

aytee111,
Thanks for your response. I am a little lost as to what you are saying I should do. From the Installation table, I can create a query listing all licenses and servers that have been used. (I think I know how to do that.) But I am not sure how to create a combobox showing all licenses that are open from the Installation table. I would need to go to the License table and compare against the list from the Installation table? Correct? As for creating a list of servers without a license? Is that not the same as the other query of comparing what is in the Installation table against what is in the Server table? Sorry for the newbie questions and have been doing app development for the past few years but have never done anything in term of Access/database or SQL.

Thanks
Denny

[aytee111]

how to create a combobox showing all licenses that are open

You have created a query showing all licenses that are currently in use, so the row source for licenses that are open will be an unmatched query between that and the license table, you can use the wizard. On the one side are all licenses being used, on the other side are all licenses not being used. Use the latter for the row source of the combobox. Make sure you take into account "open" licenses that have been used three times and exclude them from this query. Get the queries working before diving into form design.

And yes, the list of servers without a license is just that, an unmatched query between the server table and the installation.

[justme1234]

Thanks for your help. It is working perfectly.

Denny