text box if left empty populate with id

[Delid4ve]

Hi Guys.

Have a form for logging documents. One document type is employee generated and does not have a unique number(field is indexed, no duplicates)
What i need is for this document type to auto populate with a number(easiest way i see to use the document ID autonumber)
Document type is controlled by a combo box on the form.

Table: Documents
Table Fieldname: DocumentType

TextBox Name: DocumentType
Control Source: DocumentType (before this requirement)

Document Type Combox Box Name: DocumentType
Document need auto poulate for: "Engineers Log"

So i believe it needs to be something along these lines but this just shows the value, doesnt save to table, and for the false evaluation im not sure what i need here to lookup if already a value or leave blank for data entry.

=IIf([DocumentType]="Engineers Log",[DocumentID],"")

 

[gemma-the-husky]

if you have an autonumber field, and you make the control bound to the autonumber field, then it WILL populate automatically on the record insert event. make the control enabled=no

if you do not have an autonumber field, then you need code to set the id, which is probably best done in the forms "beforeupdate" event, although that means you do not see the ID until the last moment. If you do not do this, there is a possibility that another user may retrieve the same id. again make the control enabled=no.


---
your comment .... "field does not have a unique number ... "

if it's indexed, no duplicates, then it DOES have a unique number, by definition.

 

[Delid4ve]

Hi
Bear with me im a beginner

documentid = autonumber
documenttype = short text (this is indexed no duplicates in order to prevent a document being entered on the system twice)

Document types can include:
collection/returns notes - to which a unique number is assigned by the supplier
consignment notes - as above
engineers log - no unique number but require to populate one.

Have attached screenshot of form to show

 

[gemma-the-husky]

I don't understand your problem. you can set the document type to a default type in the table/field definition, but your can't otherwise automatically know which documrnt type is to be used.

most developers would have a "lookup table" for the document types, but you can do this once you get by the principle of what you are doing.

why would entering the document type prevent a duplicate?

you can have multiple documents of the same type?

 

[Delid4ve]

Think your missing what im saying.

A collection note comes in, we select the document type(lookuplist), then input the document number, etc etc

An engineers log comes in, we select engineers log from the document type, we cannot input a document number as it doesnt have one, we need to generate one, automatically in the document number field.

hence, i need to build an expression so if engineers log is selected, the document number gets autogenerated. My logice was just to use the autonumber primary key field, i dont know how to format the expression though.

I have attached picture of relationships to show.
I originally had document type in i seperate table, however i was advised that this was going over the top with normalisation by plog. hence the form set lookup list.

 

[Delid4ve]

Managed to solve this myself:

For the afterupdate of the combo box selecting the documenttype had to include:


Select Case Me.DocumentType.Value
Case "Engineers Log"
If IsNull(Me.[DocumentNumber]) Or [DocumentNumber] = "" Then Me.DocumentNumber = Val(Me.DocumentID)
Case Else
End Select