Autonumber update

[firefly2k8]

When I add a new record with:

DoCmd.GoToRecord , , acNewRec

How do i get the Autonumber to update for the new record. It is the first field of the record, Unique Project ID ?

Thanks

 

[vbaInet]

If your form is bound to the table, it will update automatically once a new record is created.

 

[Shorin]

Can you be a bit more specific? Are you using this for instance with a button on a form?

 

[firefly2k8]

Sorry. Full code is:

Private Sub Form_Open(Cancel As Integer)
  DoCmd.GoToRecord , , acNewRec
  stDocName = "CopyProjectTable"
  DoCmd.CopyObject , "Project Table " & ProjectIDText.Value, Table, "SE2 Project Table"
 
End Sub

Form's record source property is set to "SE12 Country Directory"

This adds a new record and when I enter into the form a value for one of the fields, the Autonumber populates.

Problem is when I copy the table (on opening the form - see code), I want to set its name to the new Autonumber - but this doesn't seem to get populated until I change one of the fields. So the new table name is becoming "Project Table" and not say "Project Table 4" as I want it to.

btw ProjectIDText.Value contains the Autonumber field.

 

[vbaInet]

Looks like you're following very bad database practices here. Why are you duplicating the table every time a record is created??

 

[firefly2k8]

I wish to create new tables to keep each project distinct in the first instance, i may combine to one master table later.

The forms purpose is to record the project name, id, details in a "project directory table", and to create a new table for the project.

not sure why that would be bad practise. To give some context there will only be a handful of projects per month.

 

[Shorin]

vbaInet is right in what he says. Why don't you just populate a 'Projects' table with your data (project name, id) The project details can be placed in a 'Project Details' Table????

 

[firefly2k8]

Each project involves the analysis of around 10,000 records. These need to be store in a seperate project table.

The project directory table just stores the details of the different projects - like id, date, name.

The form being discussed is used to create a new project table, and update the directory.

I may have confused matters, since the forms source is in fact set to the project directory table, not a different country table that i mentioned above.

 

[vbaInet]

What is wrong in having ProjectID, Date, Name, etc... where the ProjectID is unique for each project? A one-to-may relationship with the Projects table.

 

[firefly2k8]

i think i see what you are saying so the projects table would have multiple projects with the project id being specified for each record.

it seems to me much more intuitive to have seperate tables for seperate projects from a standpoint of segregating data, running efficient queries, inputing data, exporting data etc. the table being duplicated is a table without any data.

its the same amount of data, so why does it matter how many tables i use?

i have a fix for the problem in hand:

Private Sub Form_Open(Cancel As Integer)
  DoCmd.GoToRecord , , acNewRec
  Project_Date_Text.Value = Now
 
  stDocName = "CopyProjectTable"
  DoCmd.CopyObject , "Project Table " & ProjectIDText.Value, Table, "SE2 Project Table"
 
End Sub

 

[vbaInet]

it seems to me much more intuitive to have seperate tables for seperate projects from a standpoint of segregating data, running efficient queries, inputing data, exporting data etc. the table being duplicated is a table without any data.

If you want your database to bloat, difficulty in searching for or grouping specific project records, possible corruption to your data then please continue with your method.

 

[firefly2k8]

Thanks for your advice, I am new to all this so it is appreciated.

To save re-doing all my queries, perhaps I will create a table for the new project, and once all the processing is done, send it the results to a master table (structured as you suggest) and then delete the temporary project table.

 

[vbaInet]

Thanks for your advice, I am new to all this so it is appreciated.

To save re-doing all my queries, perhaps I will create a table for the new project, and once all the processing is done, send it the results to a master table (structured as you suggest) and then delete the temporary project table.

If you were going to do this then there's no point using a temporary table in the first place. All the processing can be done in that table and remember you can filter based on a ProjectID so that all the records you're processing are related to that projectID. This is where a subform comes into play.

The process of deleting temporary tables alone can lead to bloating.