Data Populating

[Zetony]

I want to know how I will code my program so that when a user selects the name of a town in one field a corresponding identifier (like: MU, W, PL, AC, C ect.), which is associated to that town will populate or be entered by the user. Where the user fails to enter the right corresponding identifier the program will not allow the user to continue.

[alansidman]

Do you have a table listing all the towns and their corresponding identifiers? How will this be determined if it is not already in an existing table?

[Zetony]

I have the main table and another table that has the names of all the towns. I did not write the identifiers in the table. Rather, I have the town's identifiers in a string under a private subroutines of the After-Update event of the field that should receive the identifier and the user will enter a middle and last digits which will be joined with "-" for example "LW-27-2011". The "LW" will populate as soon as the user enters the name of the town that is identified as "LW", or the user can enter this "LW". But whether it populates or is entered by the user, the program will not allow the user to enter an identifier that is different from the town he has selected from the town's list box field.

[alansidman]

If you put the identifiers in a column in the table next to the towns, then when you look up the towns in a combo box, you can automatically have the identifier show in a text box.

In the underlying query to a combo box, the columns are numbered from left to right 0, 1, 2, etc.

In the text box that you want to have the identifier appear, type
= comboboxname.column(x) where x is the column number for the identifier in the underlying query.

Alan

[Zetony]

Hi Alan,

I did exactly what you asked me to do but it did not do anything in the program. How can I solve this problem. I don't want the user to enter an identifier that will not match the town's name.

[orange]

Can you show us what you did? Any error messages?
Can you attach a mdb to show us?- don't send any confidential info.

[alansidman]

In the properties for the combo box, what are the number of columns listed in the format tab? What column are you trying to use to populate the text box?

[Zetony]

Hi Alan thank you again, there are 23 properties that are listed in the “List Box” not Combo Box. Each of these properties has been assigned an identifier as I said before. These identifiable nomenclatures will only be relevant in another field, where they will function as the first string to a number like: GW-1-2001. The “GW” is the identifier which must correspond to the name of a certain town that the user has earlier selected from the list box.

Originally I had three columns, yesterday I added one more when you said that I should enter the identifiers in the table so there are now four columns. The 1st column is the primary key, the 2nd the names of the towns, the 3rd the identifier, the forth is not much relevant in the program.

[alansidman]

In the properties for the List box, how many columns do you have listed? Same question as before. Your reference to properties are records. The properties for the List Box is found by right clicking on the List box in design view.

Alan

[Zetony]

Hi Alan is me again. I'm sorry I did not get your question right at the last reply. The column count is 2.

Thank You.

[alansidman]

Ok, you should change that to four as that is the number of columns in the underlying query. In the next line column widths, set the width of the columns you want to be visible in the list box. ie. If only the second column and third column, then you would put 0";1";1";0".

Alan

[Zetony]

Thank you to all who made contribution to my posting especially Alan. I finally did get what I wanted, this what I did.

Private Sub FILENUMBER_AfterUpdate()
Dim Munic As String
Dim FileNum As String
Dim strFirst As String

If IsNull(Me.MUNICIPALITY) Then
Munic = ""
Else
Me.MUNICIPALITY.SetFocus

Munic = Me.MUNICIPALITY.Text
End If

If IsNull(Me.FILENUMBER) Then
FileNum = ""
Else
FileNum = Me.FILENUMBER.Value
End If

If Munic = "Town’s Name" Then
strFirst = "AB-"
ElseIf Munic = "Town’s Name" Then
strFirst = "AC-"
:
:
:
Exit Sub
End If
If InStr(FileNum, strFirst) = 1 Then

Else

MsgBox "File Number must start with '" & strFirst & "'", vbExclamation, “ "

Exit Sub

End If

With this code you can populate even a number by changing the 1st ".Text" to ".Value"

I hope this would help somone else.