On click textbox inserts value back on combobox

[whitelockben]

Hi all,

I have something I want to add though and this is that on click in a text box the text box will look at another value in a combobox and based on that value with insert a specific letter.



E.g. If cboT.Value = "Electical" a P would be instered into the text box.

This field will be my key field and everything will be unique however I also want the field to auto insert the next number.

It will be made up of a number prefixed by either P or IC. So I want the db to search for the number from the record previous with the same prefix and use the next number.

Does anyone know if this is possible?

[Toyman]

Hi all,

I have something I want to add though and this is that on click in a text box the text box will look at another value in a combobox and based on that value with insert a specific letter.

E.g. If cboT.Value = "Electical" a P would be instered into the text box.

This field will be my key field and everything will be unique however I also want the field to auto insert the next number.

It will be made up of a number prefixed by either P or IC. So I want the db to search for the number from the record previous with the same prefix and use the next number.

Does anyone know if this is possible?

Put the following code in the "on click" event of the text box

Dim sPrefix as string
Dim Mysql as string
Dim sKey as string
Dim sSuffix as Integer
Dim X as Integer
Dim conn as New Adodb.Connections
Dim rs as New Adodb.Recordset

'assigns the prefix for the key based on cboTSelect case Me.cboT
Case "Electrical"
sPrefix = "P"
X=2
Case "IntCircut" 'replace this with the word represending "IC"
sPreFix = "IC"
X=3
End Select

Mysql = "SELECT Max(CDbl(Mid([YouFieldName]," & X & "))) AS NewNum
FROM tbl_YourTblName Where YourFieldName Like " & sPrefix & "*"

'open a recordset and get the max number for the group
Set conn = CurrentProject.Connection
rs.Open Mysql, conn, adOpenKeyset, adLockOptimistic
If not rs.EOF then
sSuffix = rs!NewNum
End If
Set rs=nothing

'combines the sPrefix and sSuffix to create your new key
sKey = sPrefix & sSuffix +1

'assign the sKey value to your text box
Me.YourFieldName = sKey

[whitelockben]

What a great answer, thank you very much for your help!!