Generate ID based on user selection?

[Michael]

Hello

I have a combo box containing a list of region IDs, for example

94000 = region 1
95000 = region 2
96000 = region 3

I also have property ID's that are made up from the first 2 numbers of the
region ID and adding 1 to the end for example

94001 = property 1 for region 1
94002 = property 2 for region 1
94003 = property 3 for region 1
95001 = property 1 for region 2
96001 = property 1 for region 3
96002 = property 2 for region 3

My problem is when a user needs to create a new property they select (from a
combo box) the region ID in which the property is contained (a region can
have many properties but not vice versa). I then want the property ID field
to be populated with next available property ID based on the region that has
been selected. For example, based on the ID's above if the user selected
region ID 96000 the property ID field would automatically be filled with the
number 96003 as this is the next available property ID.

I hope this is not to confusing and any help would be appreciated

 

[John W. Vinson]

Hello

I have a combo box containing a list of region IDs, for example

94000 = region 1
95000 = region 2
96000 = region 3

I also have property ID's that are made up from the first 2 numbers of the
region ID and adding 1 to the end for example

94001 = property 1 for region 1
94002 = property 2 for region 1
94003 = property 3 for region 1
95001 = property 1 for region 2
96001 = property 1 for region 3
96002 = property 2 for region 3

My problem is when a user needs to create a new property they select (from a
combo box) the region ID in which the property is contained (a region can
have many properties but not vice versa). I then want the property ID field
to be populated with next available property ID based on the region that has
been selected. For example, based on the ID's above if the user selected
region ID 96000 the property ID field would automatically be filled with the
number 96003 as this is the next available property ID.

I hope this is not to confusing and any help would be appreciated

This kind of composite field is generally considered a Bad Idea. You're really
better off with two separate fields - the property number and the region. You
can combine the two fields dynamically using a Query when needed.

To generate a sequential property ID you can use the AfterUpdate event of the
Region control. You'll be using a Form for data entry, probably with a Combo
Box to select the region. You can use code like:

Private Sub cboRegion_AfterUpdate()
If IsNull(Me!PropertyNo) Then
Me!PropertyNo = NZ(DMax("[PropertyNo]", "[tablename]", "[Region] = " _
& Me!cboRegion)) + 1
Else
MsgBox "You have already selected a region and a property number!!"
End If
End Sub

 

[Michael]

Thanks John, this works well but still has a slight problem

When I select a region from the combo box it always generates a property ID
1 greater than the largest property ID. For example, if I select region
94000 it will generate a property ID of 96002 instead of (say) 94002. 96000
is the largest region ID.

I hope that makes sense and thanks again for the help

Hello

I have a combo box containing a list of region IDs, for example

94000 = region 1
95000 = region 2
96000 = region 3

I also have property ID's that are made up from the first 2 numbers of the
region ID and adding 1 to the end for example

94001 = property 1 for region 1
94002 = property 2 for region 1
94003 = property 3 for region 1
95001 = property 1 for region 2
96001 = property 1 for region 3
96002 = property 2 for region 3

My problem is when a user needs to create a new property they select (from
a
combo box) the region ID in which the property is contained (a region can
have many properties but not vice versa). I then want the property ID
field
to be populated with next available property ID based on the region that
has
been selected. For example, based on the ID's above if the user selected
region ID 96000 the property ID field would automatically be filled with
the
number 96003 as this is the next available property ID.

I hope this is not to confusing and any help would be appreciated

This kind of composite field is generally considered a Bad Idea. You're
really
better off with two separate fields - the property number and the region.
You
can combine the two fields dynamically using a Query when needed.

To generate a sequential property ID you can use the AfterUpdate event of
the
Region control. You'll be using a Form for data entry, probably with a
Combo
Box to select the region. You can use code like:

Private Sub cboRegion_AfterUpdate()
If IsNull(Me!PropertyNo) Then
Me!PropertyNo = NZ(DMax("[PropertyNo]", "[tablename]", "[Region] = " _
& Me!cboRegion)) + 1
Else
MsgBox "You have already selected a region and a property number!!"
End If
End Sub

 

[John W. Vinson]

Thanks John, this works well but still has a slight problem

When I select a region from the combo box it always generates a property ID
1 greater than the largest property ID. For example, if I select region
94000 it will generate a property ID of 96002 instead of (say) 94002. 96000
is the largest region ID.

I hope that makes sense and thanks again for the help

Doesn't make any sense to me. What's the RowSource of the combo box (where
does it get its data)? What's its Control Source?

My *intent* - not stated very clearly I fear! - was that your table should
have *two* fields, one for Region and a separate field for PropertyID. There
would be a small table with one record per region (related one to many to the
Region field in your table), and that small table would be used as the
rowsource of the combo.

You can *display* the five digit composite number - without storing it in your
table!!! - with a textbox with a control source such as

=[RegionID] + [PropertyNo]

if the two fields are numbers.

 

[Michael]

Hi John

I think it was me that wasnt so clear. The region IDs are kept in one small
table with one record per region and the property IDs are stored in another
table. The tables (developed by someone else) do not have any relationship
between each other i.e. there is no region field in the property table,
which I think makes things a bit more difficult. My role in this project
does not allow me change the table structure in any way.

Any further ideas?

Thanks John, this works well but still has a slight problem

When I select a region from the combo box it always generates a property
ID
1 greater than the largest property ID. For example, if I select region
94000 it will generate a property ID of 96002 instead of (say) 94002.
96000
is the largest region ID.

I hope that makes sense and thanks again for the help

Doesn't make any sense to me. What's the RowSource of the combo box (where
does it get its data)? What's its Control Source?

My *intent* - not stated very clearly I fear! - was that your table should
have *two* fields, one for Region and a separate field for PropertyID.
There
would be a small table with one record per region (related one to many to
the
Region field in your table), and that small table would be used as the
rowsource of the combo.

You can *display* the five digit composite number - without storing it in
your
table!!! - with a textbox with a control source such as

=[RegionID] + [PropertyNo]

if the two fields are numbers.

 

[John W. Vinson]

Hi John

I think it was me that wasnt so clear. The region IDs are kept in one small
table with one record per region and the property IDs are stored in another
table. The tables (developed by someone else) do not have any relationship
between each other i.e. there is no region field in the property table,
which I think makes things a bit more difficult. My role in this project
does not allow me change the table structure in any way.

Well, it would have helped a bit if you had explained that fact originally.
Your table design *is wrong* and should be fixed, but apparently that's not an
option!

That said, you can construct the (improperly normalized non-atomic) combined
key with some different code. What are the actual fieldnames and control names
on your form? Do you in fact have a combo box on the form to select the
RegionID? What is its Rowsource (post the SQL), name, and control source? What
is the name and controlsource of the form control for the property ID?

 

[Michael]

Sorry about that I hadn't realised its importance until now.

Yes I do have a combo box on the form to select the region ID, its details
are

Control Source: Unbound
Name: cmbRegionID
Row Source: SELECT tbl_region.name AS NAME, tbl_region.region_id AS ID FROM
tbl_region;
Bound Column: 2

The propertyID is a text field on the form, its details are:

Control Source: property_id
Name: txtPropertyID

Let me know if you need any more detail

 

[John W. Vinson]

Sorry about that I hadn't realised its importance until now.

Yes I do have a combo box on the form to select the region ID, its details
are

Control Source: Unbound
Name: cmbRegionID
Row Source: SELECT tbl_region.name AS NAME, tbl_region.region_id AS ID FROM
tbl_region;
Bound Column: 2

The propertyID is a text field on the form, its details are:

Control Source: property_id
Name: txtPropertyID

ok... *assuming* that you want to assign a new property_id after a region is
selected, if and only if the property_id is currently null (you'ld need to
blank out the property_id before selecting a different region); and that
tblRegion_Name is Text, "94000", and PropertyID is a Text field which should
contain "94001", then put code like this in cmbRegionID's AfterUpdate event:

Private Sub cmbRegionID_AfterUpdate()
Dim iNext As Integer
Dim sLast As String
sLast = NZ(DMax("[property_id]", "tablename", _
"[property_id] LIKE '" & Left(" & Me!cmbRegionID & ", 2) & "'*"), _
Me!cmbRegion.Column(0))
iNext = Val(Mid(sLast, 3)) + 1
Me!txtPropertyID = Left(Me!cmbRegionID, 2) & Format(iNext, "000")
End Sub

Ideally you should check to see if iNext is 1000 or more, and put in
additional error trapping... left as an exercise for the user!

 

[Michael]

Thanks John

Both the Property and Region IDs are stored as numbers.

I am getting the error:

Syntax error (missing operator) in query expression '[property_id] LIKE
' &'*'

The debug arrow points to the line:

Me!cmbRegion.Column(0))

Sorry about that I hadn't realised its importance until now.

Yes I do have a combo box on the form to select the region ID, its details
are

Control Source: Unbound
Name: cmbRegionID
Row Source: SELECT tbl_region.name AS NAME, tbl_region.region_id AS ID
FROM
tbl_region;
Bound Column: 2

The propertyID is a text field on the form, its details are:

Control Source: property_id
Name: txtPropertyID

ok... *assuming* that you want to assign a new property_id after a region
is
selected, if and only if the property_id is currently null (you'ld need to
blank out the property_id before selecting a different region); and that
tblRegion_Name is Text, "94000", and PropertyID is a Text field which
should
contain "94001", then put code like this in cmbRegionID's AfterUpdate
event:

Private Sub cmbRegionID_AfterUpdate()
Dim iNext As Integer
Dim sLast As String
sLast = NZ(DMax("[property_id]", "tablename", _
"[property_id] LIKE '" & Left(" & Me!cmbRegionID & ", 2) & "'*"), _
Me!cmbRegion.Column(0))
iNext = Val(Mid(sLast, 3)) + 1
Me!txtPropertyID = Left(Me!cmbRegionID, 2) & Format(iNext, "000")
End Sub

Ideally you should check to see if iNext is 1000 or more, and put in
additional error trapping... left as an exercise for the user!

 

[John W. Vinson]

Thanks John

Both the Property and Region IDs are stored as numbers.

I am getting the error:

Syntax error (missing operator) in query expression '[property_id] LIKE
' &'*'

The debug arrow points to the line:

Me!cmbRegion.Column(0))

Assuming (again, assumptions...!!) that the table of regions contains numbers
that are multiples of 1000....

Private Sub cmbRegionID_AfterUpdate()
Dim iNext As Long
iNext = NZ(DMax("[property_id]", "tablename", _
"[property_id] BETWEEN " & Val(Me!cmbRegionID) & " AND " _
& Val(Me!cmbRegionID) + 999), 0)
Me!txtPropertyID = Me!cmbRegionID + iNext
End Sub