Problem with auto-filling bound textbox (1 Viewer)

[remcopeters]

I have a form with textbox "Quotenumber"
This textbox is bound to the field "quotenumber" in table "quotes" (in the property field "control source").
Now I want this field to be filled with this code:

=DMax("[Offertenummer]";"[Offertes]";"[Offertes].[GebruikerID]=" & [Formulieren]![Offertes]![GebruikerID])+1

I got this code in "default value" property field. This won't work if "control source" is filled in. It DOES work if control source is empty, but I need control source to hold the field of the table it needs to fill in the data.

Hope my case is clear and someone is able to help me.

 

[pr2-eugin]

That is what Default Value means.. It will place a value if there is no other value.. If there is a value it will display that value which it has.. What is that you are trying to do here??

 

[remcopeters]

I want the textbox "Quotenumber" on my form to look for the highest "quotenumber" (in table "quotes") for the current "userID" and add 1 to this. Then it needs to store this value in field "quotenumber" of table "quotes".

Example:
UserID = 2 (can be read on form)
Highest quotenumber in table "quotes" is 13
Textbox "quotenumber" on form sets its value to 14 and stores this value in field "quotenumber" on table "quotes" (as new record). Other related fields on this table store their values in this record also (already done).

 

[pr2-eugin]

Sorry I was busy I could not respond earlier..

I want the textbox "Quotenumber" on my form to look for the highest "quotenumber" (in table "quotes") for the current "userID" and add 1 to this. Then it needs to store this value in field "quotenumber" of table "quotes".

Example:
UserID = 2 (can be read on form)
Highest quotenumber in table "quotes" is 13
Textbox "quotenumber" on form sets its value to 14 and stores this value in field "quotenumber" on table "quotes"

The quote number (as mentioned earlier) would be only placed for new records, if you want to do it manually for already existing records, you should use UPDATE RecordSet.. However I am lost when you say

(as new record). Other related fields on this table store their values in this record also (already done).

 

[remcopeters]

Well......let me explain it a bit more:

- I got a table "users" with fields: userID, username
- I got a table "quotes" with fields: quotenumber, userID. Both of these are primary key.
- Each user has his own set of quotenumbers causing 2 users to both have quotenumber 3 for example
- I got a form "new quote" with a textbox "TXTquotenumber" and "TXTuserID

What the form does now:
- It looks up the highest quotenumber for the current userID with this code:

Private Sub Form_Current()
   Me.TXTquotenumber = Val(DMax("[Quotenumber]", "[Quotes]", "[TXTuserID]=" & [UserID])) + 1
End Sub

It then returns this value (say "3") and adds "1" to it so TXTquotenumber displays "4".

Problem now is that this number is EDITED in the "quotes" table which is wrong. I want it to add this new quotenumber in TXTquotenumber as a new record.

How do I do this?

 

[pr2-eugin]

- I got a table "quotes" with fields: quotenumber, userID. Both of these are primary key.

You thought to say one is Primary and the other one is Foreign??

- I got a form "new quote" with a textbox "TXTquotenumber" and "TXTuserID

What table is this form bound to??