Enable or disable textbox depending the value of another textbox 1

[ZmrAbdulla]

Hi all,

I have a databse that keep details of Keys of a building.
Some locks has 3 keys, another 4 or 6.

My requirement is below.
In the form there will be a textbox that will keep count of each keys.

ie Keynumber 1234 (3)
keynumber 4321 (6)
And there will be six text boxes that will keep the persons name who is holding the key.

I need to enable or disable the six textboxes depending on the value of the key count.
If keycount text value = 1 then
Person 1 is enabled others disabled.
If keycount text value = 6 then
all enabled. like this

Can anyone show me an example.


Thanks

Zameer

 

[PeteJohnston]

Something like

If CInt(KeyCount) < 6 Then
    Person6.Visible = False
Else
    Person6.Visible = True
End If
If CInt(KeyCount) < 5 Then
    Person5.Visible = False
Else
    Person5.Visible = True
End If
  .
  .

would work. You could also leave them visible but disable them by using .Enabled instead.


PeteJ
(Contract Code-monkey)

It's amazing how many ways there are to skin a cat
(apologies to the veggies)

 

[Edski]

Sounds like your database is Un-Normalised.

Firstly you may want to rename the field Keynumber to Locknumber. That is, a Lock may have 1 - 6 keys that open it.

For the Un-Normalised database, add the following code to the OnCurrent Event for the form

Private Sub Form_Current()

Key1.Enabled = True
Key2.Enabled = True
Key3.Enabled = True
Key4.Enabled = True
Key5.Enabled = True
Key6.Enabled = True

If Keys < 2 Then
Key2.Enabled = False
Key3.Enabled = False
Key4.Enabled = False
Key5.Enabled = False
Key6.Enabled = False
ElseIf Keys < 3 Then
Key3.Enabled = False
Key4.Enabled = False
Key5.Enabled = False
Key6.Enabled = False
ElseIf Keys < 4 Then
Key4.Enabled = False
Key5.Enabled = False
Key6.Enabled = False
ElseIf Keys < 5 Then
Key5.Enabled = False
Key6.Enabled = False
ElseIf Keys < 6 Then
Key6.Enabled = False
End If
End Sub

...where Key# is the TextBox containing the names of the employee who has the key for a particular lock.
Keys is the name of the field that determines how many keys a particular Lock has.

You should also put a reference to this code in the After Update Event for the Keys field.

If you want to Normalise your database then your form would look something like this...

In this case you would then just check that the number of related records in the child table is less than or equal to the number of keys - from the parent table, by using the Before Update Event for the Child Form.

Hope that helps

 

[ZmrAbdulla]

Thank you Edski,

It is a beautiful lesson for me
You got a star
Zameer

 

[Edski]

Thanks Zameer. For that I will add more (now that I've had a good nights sleep).

This will also do the trick (fewer lines)

Private Sub Form_Current()

Key2.Enabled = False
Key3.Enabled = False
Key4.Enabled = False
Key5.Enabled = False
Key6.Enabled = False

If Keys > 1 Then
Key2.Enabled = True
If Keys > 2 Then
Key3.Enabled = True
If Keys > 3 Then
Key4.Enabled = True
If Keys > 4 Then
Key5.Enabled = True
If Keys > 5 Then
Key6.Enabled = True
End If
End If
End If
End If
End If
End Sub

And even better...

Private Sub Form_Current()

Key1.Enabled = Keys > 0
Key2.Enabled = Keys > 1
Key3.Enabled = Keys > 2
Key4.Enabled = Keys > 3
Key5.Enabled = Keys > 4
Key6.Enabled = Keys > 5

End Sub

Of course Keys must be 'required' (non null).

The database might be better off Normalised because you may one day find that a lock has seven keys. Then you're in a pickle!

 

[ZmrAbdulla]

Hi Edski,

Thanks indeed

Zameer

 

[ZmrAbdulla]

Hi,

I have another doubt related to this.
If I query the table I get three lines of records like below

1)LockNumber, Keycount, employee1
2)LockNumber, Keycount, employee2
3)LockNumber, Keycount, employee3

How can I make a query like below
LockNumber, Keycount, employee1,employee2, employee3

Thanks
Zameer

 

[mph1]

I have a databse that keep details of Keys of a building.

If there will ever be a possibility of more than one building, then you should have a separate table for the buildings.

Just another thought.



An investment in knowledge always pays the best dividends.

by Benjamin Franklin

 

[Edski]

Zmr,
I think you're request is not possible using Access queries. I modified this code from

http://www.tek-tips.com/faqs.cfm?spid=701&sfid=3499

The results are stored in a table called tblTempHolders, which you'll need to create first.

Sub Un_Normalise_Table()

Dim db As Database
Dim rs As Recordset
Dim rs1 As Recordset
Dim Holder As String [green]' string to contain list of KeyHolders[/green]

Set db = CurrentDb

[green]'empty tblTempHolders table[/green]
db.Execute &quot;DELETE * FROM tblTempHolders;&quot;

[green]'insert list of Lock IDs into table tblTempHolders[/green]
db.Execute (&quot;INSERT INTO tblTempHolders (LockID) SELECT DISTINCT LockID FROM tblKeyHolders&quot

Set rs = db.OpenRecordset(&quot;SELECT DISTINCT LockID FROM tblTempHolders&quot

[green]'outer loop containing list of Lock IDs[/green]
Do Until rs.EOF = True

[green]'for each Lock create a string of Key Holders[/green]
Holder = &quot;&quot;

Dim sql As String
sql = &quot;SELECT KeyHolder FROM tblKeyHolders WHERE LockID = &quot; & rs!LockID

[green]'select list of Key Holders for current LockID[/green]
Set rs1 = db.OpenRecordset(sql)

[green]'inner loop to create a concatenated string of Key Holders[/green]
Do While rs1.EOF = False
Holder = Holder + &quot;, &quot; + rs1!KeyHolder
rs1.MoveNext
Loop

If Len(Holder) > 0 Then
[green]'trim off leading comma[/green]
Holder = Right(Holder, Len(Holder) - 1)

[green]'update Key Holder List with Holder string[/green]
db.Execute (&quot;UPDATE tblTempHolders SET KeyHolder = '&quot; + Holder + &quot;' WHERE LockID = &quot; & rs!LockID)
End If

[green]'update Key Count[/green]
db.Execute (&quot;UPDATE tblTempHolders SET KeyCount = &quot; & rs1.RecordCount & &quot; WHERE LockID = &quot; & rs!LockID)

[green]'move to next Lock[/green]
rs.MoveNext
Loop

[green]'release the memory[/green]
Set rs = Nothing
Set rs1 = Nothing
Set db = Nothing

[green]'Have a look![/green]
DoCmd.OpenTable &quot;tblTempHolders&quot;

End Sub

 

[ZmrAbdulla]

Hi Edski,
Thanks

I will have to study this and execute. It may take few days because I am currently working on another database.

Zemeer Abdulla