Auto Number Assistance 5

[ericpsu32]

Hi, I am trying to start a field at a specific number and have that field increment each time a new record is entered. I was hoping I could do this through the use of an autonumber, but I don't know how to get it to start at a specific number.

I would really appreciate some help on this.

Thanks!

 

[PaulBricker]

Open your database. When you see the database window, click on Help. Type in AutoNumber in the Index box. Then look for a topic called
"Change the starting value of an incrementing AutoNumber field"

This will give it to you better than I can type it out. If you have problems finding it, post back and I will copy and paste the instructions here.

Paul

 

[ericpsu32]

Thanks!

 

[PaulBricker]

No problem. Didn't want to sound weird about it but if you don't open the Help file in the right window, you'll never find the article (Only MS would make that improvement).

Good luck.

Paul

 

[jbento]

I needed the same information.

I gave you a star Paul.

Thanks,

Jerome Benton
JERPAT Web Designs

http://www.jerpat.org

GOD Is Good All The Time!!!

 

[jbento]

Paul,
I further started to follow the instructions in the help area, but it is not making sense.

I have a new table with no records, and I tried to follow the following instructions from the help: "For a new table that contains no records, you can change the starting value of an AutoNumber field whose NewValues property is set to Increment to a number other than 1"

From the above instructions, I don't have a clue of how to set the number to a number I desire, or where to set this number.

Could you help me figure this out?

Thanks,

Jerome Benton
JERPAT Web Designs

http://www.jerpat.org

GOD Is Good All The Time!!!

 

[MinusM]

Just try running this code.

INSERT INTO Table1 ( ID )
SELECT 12 AS Expr1;

Here 12 is the required starting no

You can insert a number 1 less than you want and delete that line. Then Access will take care

Best of luck

 

[PaulBricker]

Jerome, post back if MinusM's advice didn't get you where you need to be. Also, post the value you want to start the AutoNumber at if you need more help.
Good luck.

Paul

 

[jbento]

MinusM,
Where do I place this code?

Is this placed somewhere in the table design or is it placed on the form?

I would like for my number to start at 101.

Thanks MinusM and Paul Bricker,

Jerome Benton
JERPAT Web Designs

http://www.jerpat.org

GOD Is Good All The Time!!!

 

[jbento]

I figured it out guys.

YOU ALL HAVE A BLESSED HOLIDAY!!!

Jerome Benton
JERPAT Web Designs

http://www.jerpat.org

GOD Is Good All The Time!!!

 

[haoose]

Hellow everyone,

What if I have created a database and filled it with bogus data just to verify my design. How can I erase all the bogus data (this part is simple) and resest my autonumber to zero so that I can start entering real data in my database?

I've tried using the "Change the starting value of an incrementing AutoNumber field" in access help menu, I've checked to make sure there aren't any primary keys and other Required properties. Also I was able to append a 0 but then when I entered new data the database returned to the old autonumber sequence.

Thank you in advance for any help.

Oliver

 

[PaulBricker]

To reset an Autonumber field to 0, you delete all the records and the do a Compact and Repair. That will reset your starting value back to zero.

Paul

 

[haoose]

Hey Paul,

Thanks again for the help. Maybe you can help me with this other problem I'm having. I've gotten help before but I'm still not clear.

I've created a report that when opened (double clicked) first opens an entry form. When the user enters the data the report is generated.BUT, if the user decides to close the form (i.e. cancel or doesn't want the report anymore) then the form closes and some generic access entry form appears awaiting for the entry data.

How can I make sure that when I close the form, the report is closed as well and no entry prompt appears?

I really appreciate the help.

Oliver

 

[PaulBricker]

Well, I'm not sure what will work best. It would help to know how the user opens the report and where the code to open the form is, but I would make sure your users can't just close the form without some action on their part to deal with the situation. For example you could put a button on the form that says
Cancel Report

and then in the Click event for the button you can try
DoCmd.Close acReport, "ReportName"

That may or may not do it. The report may be in a state at that point that you can't just cancel it.

You can try changing the way the report is run by having the user open the Form first and then put a button on the form that opens the report based on the values entered in the controls on the Form. Then if they just decide to close the form nothing happens at all.

Give me a little more info and I might be able to be more specific.

Paul

 

[haoose]

Paul,

The user opens a (there are 4 different reports each with a different criteria) report by double clicking on it in the main window. The code that opens the form is in the report "On Open", "On Close", etc.

I have included a command button on the form that cancels the form but like you mentioned this didn't work. I have tried DoCmd.Close acReport, "ReportName" but it seems that once you open the report, which opens the form you can't close the report by simply closing the form. I think you are right about being in a certain state.

Now I didn't want to open the form first and then have a command button that opens the report because I have more than one report.

Also, I spoke to someone about this before but I wasn't really clear on what to do. They basically told me something about making the report invisible/visible (I don't remember which one) and then making it visible/invisible. Maybe this reminds you of something.

Thanks again for your help.

Oliver

 

[PaulBricker]

Sorry for taking so long to get back to you Oliver. I have not found any way to cancel the report once it is opened from the main window. Whether the report is visible or not, it is still going to request parameters because the form is no longer there to pass the values it's looking for. I have been very busy the last week and haven't really spent to much time on this. If things slow down this week, I may have a chance to persue it further, but I don't think I'm going to have any more luck than what I've already found out.

Pau

 

[RoyVidar]

Hi haoose - I might have something.

The approach of opening a form from the report isn't my favourite either, but there are some tweaks that might apply.

First: The form needs to be opened in dialog mode, and the form opening must be placed in the reports on open event. From this event, one might cancel the report opening.

Next, how to prevent the opening of the report: Use an "IsLoaded" function, for instance the one in the Northwind sample database, or from Rohdems faq (faq181-320). Copy the function to a module (not a forms/report module but from the VBE Insert | Module).

Then use for instance the two lines below in the reports on open event.

[tt]docmd.openform "Criteriaform",,,,,acDialog
Cancel = Not Isloaded("Criteriaform&quot' cancel opening if form not open[/tt]

Then, to have the possibility of both opening the report or closing the report, simply:

In the close buttons on click (Cancel):
[tt]docmd.close acform, me.name ' closes the form[/tt]

In the "open report" buttons on click (Ok):
[tt]me.visible = false' hides the form, which "removes" the dialog state
'of the form, thus letting the report continue opening [/tt]

Then, to close the criteria form, I'd recommend using the reports on close event (again, for safety, using the IsLoaded function):

[tt]If IsLoaded("Criteriaform&quot Then
DoCmd.Close acForm, "Criteriaform"
End If[/tt]

- so I think what you heard somewhere, concerning visible, probably was a reference to the criteriaform, not the report

One final note:
If you later decide to open this report from a button on another form, you'll probably have to deal with a runtime error 2501 - operation cancelled. To trap for this, here's a sample routine only opening the report and some errorhandling:

[tt]Private Sub MyOpenReport()
on error goto MyErr
docmd.openreport "MyReport", acViewPreview
MyExit:
exit sub
MyErr:
if err.number<>2501 then
msgbox err.description
end if
resume MyExit
End Sub[/tt]

Report back if problems.

HTH Roy-Vidar

 

[haoose]

Roy-Vidar,

Thank you so much. I had most of the code you recommended but only because the wizards had included it. You description helped me understand it all and most importantly: the IsLoaded function made it all work.

Take care and Thanks again.

Oliver

 

[PaulBricker]

Gets a star from me as well.

Paul

 

[haoose]

Hello everyone,

I'm having problems within the IsLoaded function. Initially I was using Access 2002, but now I have to install the database in a computer with Access 2000. When I select "Reports" (in 2000 version), my date range entry form appears (as expected), but if I try to preview a report, or exit the form, I get a compiler error and the debugger is called.

The error states that there is an undefined variable and it points to the IsLoaded function...specifically to 'acCurViewDesign' in the code below:


Option Compare Database
Option Explicit

Function IsLoaded(ByVal strFormName As String) As Boolean
' Returns True if the specified form is open in Form view or Datasheet view.
Dim oAccessObject As AccessObject

Set oAccessObject = CurrentProject.AllForms(strFormName)
If oAccessObject.IsLoaded Then
If oAccessObject.CurrentView <> acCurViewDesign Then
IsLoaded = True
End If
End If

End Function

How can I correct this problem?

Thanks