Finding a match between a TextBox Value and ListBox Values 2

[supanoods]

Hi guys,

I have a form (FormA) that after a couple of criteria filters, displays failure codes for a system. The user then double clicks a code and a popup form (FormB) appears that is half filled in, and the user finishes it off and submits a change in status of that code. Back on FormA, a list box is requeried and displays which codes have been changed.

What I need to do is stop users repeating a code change. I have a TextBox named CodeChoice (displays the code selected), and the ListBox is named CodesDone. If the value in CodeChoice matches any of the codes listed in CodesDone, then I would like a msgbox to appear and stop FormB appearing.

Can any one give me an idea of the vba code that will do this, or point me in the right direction. This is the last part of my project, and it would be fantastic to get it done (its driving me nuts!).

Cheers, Supanoods B-)
"If it aint broke - dont fix it!

 

[TheAceMan1]

How are ya supanoods . . .

Try this in the [blue]DoubleClick event[/blue] of the code textbox ([blue]you![/blue] substitute proper names in [purple]purple[/purple]):

[blue]   Dim db As DAO.Database, rst As DAO.Recordset, SQL As String
   Dim Msg As String, style As Integer, Title As String, DL As String
   
   DL = vbNewLine & vbNewLine
   Set db = CurrentDb
   SQL = "SELECT TOP 1 [purple][b][i]CodesFieldName[/i][/b][/purple] " & _
         "FROM [purple][b][i]TableName[/i][/b][/purple] " & _
         "WHERE ([[purple][b][i]CodesFieldName[/i][/b][/purple]] = [red][b]'[/b][/red]" & Me!CodeChoice & "[red][b]'[/b][/red]);"
   Set rst = db.OpenRecordset(SQL, dbOpenDynaset)
   
   If Not rst.BOF Then
      Msg = "Code '" & Me!CodeChoice & "' already exist!" & DL & _
            "Try a different code! . . ."
      style = vbInformation + vbOKOnly
      Title = "Duplicate Code detected! . . ."
      MsgBox Msg, style, Title
   Else
      DoCmd.OpenForm "formB"
   End If
   
   Set rst = Nothing
   Set db = Nothing[/blue]

Note: if [purple]CodesFieldName[/purple] is numeric, remove the two single quotes in red [red]'[/red] . . .

See Ya! . . . . . .

 

[supanoods]

Thanks for the speedy response, but when I tried to run this I get "User-Defined type not defined" regarding the DAO.Database declaration!!!

Any Ideas???

Cheers, Supanoods B-)
"If it aint broke - dont fix it!

 

[PHV]

User-Defined type not defined
menu Tools -> References ...
Tick the Microsoft DAO 3.? Object Library

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[TheAceMan1]

supanoods . . .

in any code module on the MenuBar click [blue]Tools[/blue] - [blue]References[/blue].

In the listing your looking for [blue]Microsoft DAO 3.6 Object Library[/blue]. Put a check in the box and click OK.

See Ya! . . . . . .

 

[supanoods]

Thanks guys, that has got me past that particular problem, but now i get
"Run-time error '3061': Too few parameters. Expected 3."

This is at the line:
Set rst = db.OpenRecordset(SQL, dbOpenDynaset)

(dbOpenDynaset = '2')

I'm afraid my coding knowledge is not at this standard - so any help would be excellent!

Cheers, Supanoods B-)
"If it aint broke - dont fix it!

 

[supanoods]

Guys,

Just to let you know that I managed a kinda work around this using sencondary query and a subform. It was lenghty, but will do

Thanks again

Cheers, Supanoods B-)
"If it aint broke - dont fix it!

 

[Remou]

It should be possible to use DlookUp.