opening a form and presetting value

[jillp]

This seems such a simple request, but I can't figure it out.


I open a form and I just want to put a value in one of the fields.

Help all you lovely people.


I have

docmd.openform "formname"

but, what do I do next ?

Thanks

[pbaldy]

Forms!FormName.ControlName = YourValueHere

[Missinglinq]

...I just want to put a value in one of the fields...

What Value? Which Control? And, most importantly, which Record!

A much better, plain-language, explanation is needed, outlining exactly what you're trying to do, in order for us to help you!

Linq ;0)>

[jillp]

The form is called frmcontacttype and only have 2 fields, contacttype and contactnote

I want to make contacttype the next number from those entered.

I have got that value by counting the exisiting number of entries and adding one.

so I currently have this which opens the form and sets typecount with the number I want.

Dim typecount
    
    typecount = DCount("ContactType", "tablecontacttype", "ContactType") + 1
    
    DoCmd.OpenForm "FrmContactType"

What I want to do now is make contacttype the value of typecount so that the new form has a suggested number in it.

Thanks

[smithse]

Hi jillp,

Instead of adding the value of the count of the records+1 (which can result in errors in a multi-user database) the other option is to use an AutoNumber field which will increment the number when the record is created.
Also, on setting this value, if I was doing it this way, I would run a query just before I inserted the row, rather than when the record was created on an unbound form.

I use a table which stores a prefix, number and suffix to assign numbers to a record. My function takes the last number, adds 1, and updates the last assigned number back to that table for the next user to create an event number.

[jillp]

Hello smithse
My knowledge of access and creating databases is limited, I am used to working on ones that already exist so I am sure I am not doing the most sensible things.
If I use autonumber, do I just get the user to input the note and then allow the contacttype number to be created when it is saved.
How would I display this number whilst they are typing the note ?
Also, the other method of using a table, how would I even go about doing this.

This database will only be updated by one person, although I will be doing lots of reports from the data for others once it is ready so I hadn't even thought about the multi user optons.

[smithse]

Hi again jillp,

I made a comment in a recent post of mine that we were all new to Access at one point or another, so don't feel bad about asking questions!

Basically, an AutoNumber field is an incrementing field or column, that will create the next number in sequence when a record is created.

If you have a form bound to a table, and a textbox for example bound to the autonumber field, when you first create the record, that textbox will display "(New)" instead of a number.
Once you start editing the record, e.g. start typing text into the textbox that is bound to your notes column or field, the value of the textbox will change automatically to the next number in sequence.

Note though, that if you cancel the record for whatever reason, the number that was assigned to that record will be discarded along with the record. For example, you're editing a record whose AutoNumber field was '9' and you cancelled it, or deleted it, the next record you create will be '10' and not '9'.

I hope that makes a bit of sense.

[jillp]

Yes, this does make sense, but is there a way if the user hits a cancel button of getting rid of that number generated.
The actual number doesn't matter in this instance as another table will be allowing the user to choose a note from a list, so this is just an academic question.
This table is just a reference list that will be created initially and will rarely if ever have new entries.

[smithse]

jillp,

Yes, there is a way
If the number is generated, and the user hits the cancel button, then the number will never be seen in a row in the table (i.e. the row never gets inserted into the table), but will never be created again.
What version of Access do you use?

[jillp]

smithse,

I am using Access 2007.
I now have the first form working
Thanks mostly to help on here.
It automatically generates the next number, opens the form, saves & closes or cancels and does not save.
Doesn't sound much, but to me it is a real achievement.

Now I need to do a modification form so that any of the existing entries can be changed.

Then on to the rest of the database, but having done this I now feel somewhat more confident.
I am quite happy with designing the db as I have used them for many years, but actually creating is new to me.

Thank you for all your help.

JIll

[smithse]

Hey Jill,

Happy to help. If you need any more, just yell.

Also, just note, that now you're modifying it, an 'AutoNumber' field, cannot have it's value edited ;-)

[Missinglinq]

Autonumbers are intended to be used for one purpose and only one purpose, to provide a unique identifier for each record. They should not be used as account numbers, serial numbers, etc. Here's a post I've archived from a gentleman named John Vinson, MVP, explaining how Autonumbers work:

When using Autonumber, do be aware that there will be gaps in the numbering any record that's deleted will leave a gap; hitting <Esc> after starting a record will leave a gap; adding records using an Append query may leave a gap, often a huge one; replicating the database will make your invoice numbers random.

In short... it's best NOT to use Autonumbers for human consumption, and particularly not for consumption by accountants and auditors. Invoice sequences like 319, 321, 322, 385, 386, 221841246, 1083225152 make such people get very nervous.

If you want to your 'ContactType' numbers to be consecutive you'll have to redefine the Datatype of the Field to Text or Number/Integer (despite the "numbers" it contains, these type of Fields are frequently defined as Text) and develop a hack for generating an auto incrementing 'ContactType'.

Note that these hacks cannot be used with Data Entry set to Yes! In that case the RecordCount is always 0 when a new session is started, regardless of the number of Records in the Table.

Here's a typical Auto-Incrementing Number hack. The first code here would be for an IDNumber that is defined in the Table as Text Datatype.

Private Sub Form_BeforeUpdate(Cancel As Integer)

If Me.NewRecord Then
   If RecordsetClone.RecordCount = 0 Then
   Me.IDNumber = "1"
  Else
    Me.IDNumber = DMax("Val([IDNumber])", "YourTableName") + 1
  End If
End If
End Sub

Here's the same code for an IDNumber defined as a Number:

Private Sub Form_BeforeUpdate(Cancel As Integer)
If Me.NewRecord Then
  If RecordsetClone.RecordCount = 0 Then
    Me.IDNumber = 1
  Else
    Me.IDNumber = DMax("[IDNumber]", "YourTableName") + 1
  End If
End If
End Sub

The above has been used safely for Multi-User environments, in my experience, because it doesn't assign the ID number until the very last second before the record is saved. This is necessary to decrease the chance of two users getting the same ID number. The only drawback is that the user cannot see the ID number until the record is saved.

In a single user environment, the code can be moved to the Form_Current event and the ID number will appear as soon as a new record is started.

Linq ;0)>

[jillp]

Lingq,

Wow, it never occurred to me how much there was to think about when I agreed to create a database.

Luckily in this instance their are no accountants involved, just a marketing manager !!!!

But, before I create my next table, which is a score and note about each score I shall think very seriously about how I do it.

Thanks
Jill

[Missinglinq]

See, you've already learned the most important thing about Databases!

Planning is all!

Good luck with your project!

Linq ;0)>