YES/NO function

I'm working on a database which contains IP Address, i want to program the database so that when a user save a IP Address, the used IP Address is taken off a combo box, so the next user won't use the same IP Address again. I know i should use a yes/no datatype in a table but i'm not sure how to program it so it changes when the save or delete button is pressed in a form.
Can anybody help me out there.

Hali,

The solution I am recommending below is likely simple, but quite impressive.

Let's say you have a table with the IP addresses called tblIPAddress. It has the fields:
IPAddress (type: text, length: 15, AllowDuplicates: False)
Occupied (type: Yes/No)

The Row Source property of the combo box should be:

SELECT IPAddress FROM tblIPAddress WHERE Occupied=False

and for more convenient usage you may append the order by clause to it:

ORDER BY IPAddress

When someone selects an IP address, you need to set the Occupied field to True for that IP address. To do so, include the following code into the OnClick event handler of your Save button:

'***************************************
Dim MySQL As String
MySQL="update tblIPAddress set Occupied=True where IPAddress='" & cmbComboBox & "'"
DoCmd.RunSQL MySQL
OtherControl.SetFocus 'remove the focus from the combo box before refreshing the combo box
cmbComboBox.Requery 'this line is the key step. This refreshes the content of the combo box, so the selected IP address will be removed from the combo box.
'***************************************
where cmbComboBox is the name of the combo box, and OtherControl is any other control on your form that can get the focus (ie. a Label cannot get the focus).

When a user deletes a record, then the IP address of the deleted record is not occupied, so it should again appear in the combo box, so you need to set the Occupied field to False for that IP address. To do so, include the following code into the OnClick event handler of your Delete button:

'***************************************
Dim MySQL As String
MySQL="update tblIPAddress set Occupied=False where IPAddress='" & cmbComboBox & "'"
DoCmd.RunSQL MySQL
OtherControl.SetFocus 'remove the focus from the combo box before refreshing the combo box
cmbComboBox.Requery 'this line is the key step. This refreshes the content of the combo box, so the released IP address will be included in the combo box.
'***************************************

Note: this code MUST precede your 'record deletion' command line.


I hope it helps you.

BRegs,

TBÁrpi

Thank you for the code.
I try the code you gave me, and when i click on the save button a message came up saying:

'You are about to update 0 row(s)'

I know there something wrong, can you explain why i'm getting the message please

cheers

Hali,

There are more ways to avoid getting this message, I put here three:

1. Go to Tools->Options menu path in Access, and select Edit/Find tab. Remove the tick from the check box Action Queries (may be different in English version I have Hungarian one...). Note that this way the setting will only be applied the Access running on YOUR machine.

2. Insert the following line preceding the DoCmd.RunSQL MySQL command:
'***************************************
Application.SetWarnings False 'This command switches off ALL warning messages, NOT only Action Queries confirmation!
'***************************************

And insert the following line following the DoCmd.RunSQL MySQL command:
'***************************************
Application.SetWarnings True
'***************************************

3. If you wish to eliminate ONLY the Action Queries confirmation from your code, insert this line:

'***************************************
Application.SetOptions "Confirm Action Queries", False 'this switches off exactly the message you got, and ONLY that.
'***************************************
'***************************************
Application.SetOptions "Confirm Action Queries", False 'and this switches back on
'***************************************

Regards,

TBÁrpi

Sorry, the last sample code is:
'***************************************
Application.SetOptions "Confirm Action Queries", True 'and this switches back on
'***************************************
So put True as the second parameter instead of False...

TBÁrpi

Anyway, it should have told you 'You are about to update 1 row(s)' and not 0! Something's still not perfect with my code... If the selected IP address does not disappear from the cmbo box, let me know.

TBÁrpi

The code you gave me to get rid of the error message works but i still can't get the yes/no data type to change in the tables.

I was wondering the code with this in:

IPAddress='" & cmbComboBox & "'"

What does it mean so i know if i'm doing it right, i think its part of this code thats not allowing me to update my tables.

What do you think TBÁrpi

Forget about the last message, my mistake got it working now cheers for the code.

Hi,

The SQL statement we use to change the value of the Occupied field for a specified IP address is a string used as parameter for the DoCmd.RunSQL command. This string is built of two parts:

1. The constant part of the SQL statement, which is:
"update tblIPAddress set Occupied=True where IPAddress='"
Constant means that this part of the SQL statement is static, in other words it remains unchanged.

2. We need to specify which IP address is Occupied. And this is not constant, because it's selected by the user. So we need to read the selected IP address from the combo box and pass it to the

WHERE clause of the SQL Statement.
So the second part of the SLQ statement must be the value selected in the combo box. To pass the value, we need to refer to the name of the combo box. So the second part is the name of the combo

box.

To concatenate the first and second part of the SQL statement, we use the & sign. If we refer the name of the combo box, it returns the value stored in the combo box.


Example:
Let's say the selected IP address in the combo box is 62.201.62.216. The name of the combo box on the form is cmbIPAddress. Then the command line we use in the code will be the following:
MySQL="update tblIPAddress set Occupied=True where IPAddress='" & cmbIPAddress & "'"
and the SQL statement that will be executed is the following:
update tblIPAddress set Occupied=True where IPAddress='62.201.62.216'

You probably noticed that there is an aphostrophe (') between the equal sign and the quotation mark, and there is an other aphostrophe between two quotation marks at the end of the SQL statement.

This is needed because 62.201.62.216 is a string value, and string values must be palced between aphostrophes in an SQL statement.

But make everything clear I put something together for you. Download the attached zip and run frmOccupation. To see how it worrks, investigate the code behind the form. It became a bit more

difficult than the codes I sent earlier. The code may be optimized, but I just wanted to send a quick answer.

The converted the sample to Access97, because I didn't know which version are you using.

BRegs,

TBÁrpi