Importing FileMakerPro data inot Access - Help please

  • Thread starter Thread starter Mike
  • Start date Start date
M

Mike

Hi,

I have exported FileMakerPro data into a .dBF file, and
imported the same into Access, no problems but some minor
changes. i do have a problem that I don't know how to
address:

The fileMakerPro was generating a number sequentially and
automatically, a number like this: 00013725 which is a
proposal number each time a record is being added, since
it has been recommended to me not to use AutoNumber of
Access on forms so that User can see, I was wondering
what other options do I have to have Access generate such
number?

Thank you in advance.

Mike.
 
Mike said:
Hi,

I have exported FileMakerPro data into a .dBF file, and
imported the same into Access, no problems but some minor
changes. i do have a problem that I don't know how to
address:

The fileMakerPro was generating a number sequentially and
automatically, a number like this: 00013725 which is a
proposal number each time a record is being added, since
it has been recommended to me not to use AutoNumber of
Access on forms so that User can see, I was wondering
what other options do I have to have Access generate such
number?
Click to expand...

There are a variety of ways of generating such a number but does it have a
real use?
Do clients refer to this number?
Does it have to start over for some reason?
Does it have to be sequential.
What happens if the record associated with this number is deleted?
Can some other sequence be used (E.G. ClientID and Date )
 
This number is a proposal number and customers are
refered by this number. When we make a proposal to get a
job, this number created for the new record which
contain, customer information, contact information, job
location addresses. After this proposal becomes ours,
i.e. when we win the bid, we add a job number, a number
such as 1238 (job number 1238) to this number and save
the record with its status change to "active job". We
cannot and do not want to delete the record associted
with this number.

I hope that I have provided you with enough information
for a proper procedure to generate this number.

Thank you inadvance for you help.

Mike
 
Mike said:
This number is a proposal number and customers are
refered by this number. When we make a proposal to get a
job, this number created for the new record which
contain, customer information, contact information, job
location addresses. After this proposal becomes ours,
i.e. when we win the bid, we add a job number, a number
such as 1238 (job number 1238) to this number and save
the record with its status change to "active job". We
cannot and do not want to delete the record associted
with this number.

I hope that I have provided you with enough information
for a proper procedure to generate this number.

Thank you inadvance for you help.

Mike
Click to expand...

You would have to add the job number as a separate field but could
concatenate it in a query.
I would use an auto number for the proposal in this case. You could use
MoveLast and get the last proposal number and add 1 to it or maintain that
number in a separate table. (Buy Access Developers handbook and use their
code.)
There are trade offs in any case.
 
Mike,

I have the Access 2000 bible book, it would be very
helpfull if you could post the code (if you have it)as my
boudget is kind of tight at this time to go and buy the
book.
Also, How can I use the MoveLast to get the last proposal
number? can you please explain it more specific.

Thanks,

Mike
 
Mike said:
You would have to add the job number as a separate field but could
concatenate it in a query.
I would use an auto number for the proposal in this case. You could
use MoveLast and get the last proposal number and add 1 to it or
maintain that number in a separate table. (Buy Access Developers
handbook and use their code.)
There are trade offs in any case.
Click to expand...


Because of the unpredictability of Autonumber values, perhaps this
might not be too good an idea!

Autonumber is designed only to provide a unique identifier for records
in a table. This is used internally by Access in building relationships
between tables according to your design. The actual values that
Autonumber delivers are unimportant and should never be exposed to
users or have any external meaning at all. Depending on the design and
use of the database, Autonumber will develop gaps, become
random and go negative.

For any number series that you want to control or have available
externally, you must design your own. There are several schemes
available depending on what you want to do.

Here is some code to generate sequential numbers. It works for both
single and multi-user scenarii.

You will need a new separate Table (tblSeries) having just one Field
(NextNumber) of type Long Integer to store the incrementing number.
The Table will have no index and will not be related to any other Table.

Dim rst As Recordset, db As Database
Dim lngNextNumber As Long
Set db = CurrentDb

'Open tblSeries, lock, read next number, increment, update and unlock.
Set rst = db.OpenRecordset("tblSeries", , dbDenyRead)
With rst
.MoveFirst
.Edit
lngNextNumber = ![NextNumber]
![NextNumber] = lngNextNumber + 1
.Update
End With
rst.Close
Set db = Nothing

This code is designed for Access 97. If you are using a later version,
you will need to use DAO.Recordset and DAO.Database and make sure you
have the appropriate reference set for the DAO Library.

The incrementing number is returned to you in the variable
lngNextNumber.

This code should be in the Click event of the command button you use
finally to commit your numbered record. This must happen after all
opportunities to abort your record, otherwise you could have gaps in
your numbering to explain to your auditors! After this code, you will
need to store the number value in your Table holding your records.

If, in your system, you need other unrelated number series, just add
another Field to tblSeries and manage it in a similar manner.

hth

Hugh

hth

Hugh
 
Back
Top