Increment field by 1, add to table

[MrsTFB]

Hi guys,
I have a database that assigns RFQ (Request for Quote) number. What I need it to do, is find the maximum number in the RFQnum field and add 1 to it for the new record. I've achieved creating the number, but it didn't populate the new record with the new number.
I'm missing something.
Can you please help me.
Thanks,
Mrstfb

 

[amorous]

Show us your code.

-VJ

 

[MrsTFB]

Thanks for the interest! The code didn't work, and I don't have it...what I was trying was On Load set RFQnum.value = RFQnum +1.

I'm not very familiar with coding in Access, so my knowledge is limited. I have also created a query that will give me the maximum number in the field of RFQnum, but I can't figure out exactly how to use that.

Thanks,
MRstfb

 

[LittleSmudge]

Try

RFQNum = DMax("RFQNum","RFQTable")


That is
ControlNameOnForm = DMax(FieldNameInTable,TableName)

But why not just set the RFQNum to type AutoIncrement and it will do it for you.



'ope-that-'elps




G LS
spsinkNOJUNK@yahoo.co.uk
Remove the NOJUNK to use.

 

[LittleSmudge]

Oops - that of course should have been

RFQNum = DMax("RFQNum","RFQTable") + 1





G LS
spsinkNOJUNK@yahoo.co.uk
Remove the NOJUNK to use.

 

[MrsTFB]

I was just wondering where the +1 was..lol!
I'm actually playing around right now with auto increment. It's working, I even built the update query to start at the new number.
However, on the form, it says "(Autonumber)" in the form until information is entered. It might be petty, but how can I get around that??? It's confusing or ugly if nothing else.
Thanks, I'm getting there...gonna try your code. Is that in the Control SOurce in Properties??
I'll try it.
Thanks greatly,
MRSTFB

 

[LittleSmudge]

I'd stick it in the Form's Onload event

If IsNull(RFQNum) Then ' This is a New RFQ
RFQNum = DMax("RFQNum","RFQTable") + 1
End If




If you are looking at the autonumber - then how about making the control hidden ( Visible = No ) until someone enters data in another control. Put the RFQNum.Visible=True in the other control's AfterUpdate event.






G LS
spsinkNOJUNK@yahoo.co.uk
Remove the NOJUNK to use.

 

[lespaul]

But remember if you use an autonumber and then delete the record you WILL NOT have a sequential number any more. Pretend you start with a new table and add 1000 records. Your autonumber field will be consecutive. Then you realize that the last 200 records were wrong and need to be deleted. So now the autonumber field is consecutively numbered from 1 - 800. Add a new record and it will have number 1001. So, if the RFQNumber is something that has "Meaning", you shouldn't use the autonumber.

Leslie

 

[MrsTFB]

It doesn't REALLY matter if it skips numbers...it just has to increment. It is only an identifier. However, I don't want that autonumber to show in the box before the information is entered. ANy ideas on that?
MrsTFB

 

[LittleSmudge]

Yes, YES, YES

I've sussed it.

Have your AutoNumber primary key bound to a text box control
Set the Visible property of that control to NO
Call the control 'RFQNo'

Shift the control somewhere out of the way.
As long as it is on the form it doesn't matter where it is.

Add an unbound text box in the position that you want the RFQNum to appear. Call this control RFQNoShow
Set the Locked property of RFQNoShow to Yes

Set the ControlSource property of this new text box control to
=IIf(IsNull([RFQNo]),"",[RFQNo])


and YES! It stays blank until the user add data elsewhere in the form - then it magically displays the RFQNo



'ope-that-'elps



G LS
spsinkNOJUNK@yahoo.co.uk
Remove the NOJUNK to use.

 

[lespaul]

ooooh, magically! I'm impressed!!!
[ROFL]

les

 

[LittleSmudge]

Any sufficiently advanced technology is indistinguishable from magic.
Arthur C. Clarke

G LS
spsinkNOJUNK@yahoo.co.uk
Remove the NOJUNK to use.

 

[lespaul]

That's exactly right! I love that quote!

Think what a cave man would say to electric lights!

Have a great weekend, I'm OUTTA HERE!!

les

 

[MrsTFB]

perfect! thanks!

mrstfb