Mental Block

[Acropolis]

I could do with a little help, the solution is going to be so simple, but at the moment I am having a mental block and cannot think how to get past this really simple problem.

I have a table with a list of MAC Addresses in it, 4 fields ID | MACAddress | Issued | DeviceID

I have another table that holds the devices we make, I need to be able to issue out MAC Addresses to the devices.

I have a form, which you enter the device serial number into, if it already has a MAC assigned it will return the MAC address for that device, if not I want to assign the next MAC Address in the list that isn't assigned. But for some reason my brain isn't working this morning and I can't get it. :banghead:

I have this working in Excel nicely already, but need to bring it into the system now.

Thanks in advance

 

[pr2-eugin]

I am not sure how you have mapped this out in Excel, but in Access I can see a couple of ways you can do this. Using DLookUp, checking if the ID exists in the table, if so no problem. If not get the Max(ID) in the other table to fetch one new MAC ID. (More info. needed about other table to give an exact solution).

First, I need to see how you have this sorted in Excel.

 

[Acropolis]

In excel it was fairly simple, I had a front page with 2 text boxes. The first one you enter the device serial number, then press a button. It finds the serial number from a list on another sheet, if it finds it, it returns the value of the MAC Address, if not if finds the next available MAC address and put that value in the second text box.

The method I used for finding the next available, was to start at the bottom of the column, come up to the last used row, then offset down one row, then offset a couple of columns to get the MAC Address.

it worked well in Excel.

In the Access system, I have it working to return the MAC address if one has been assigned to a device already, that's working fine, just for some reason I am struggling to get my head round finding the next available MAc Address from tblMACAddress.

The table is made up as follows:

ID | MACAddress | DeviceID | Issued | IssuedByID | IssuedWhen

ID = primary key for table
MACAddress - Pre populated list of the MAC addresses available, there are 1024 in there
DeviceID - This is what I need to enter when assigning the next available MAC to a device. Foreign key to tblDevices
Issued - Yes/No
IssuedByID - Foreign key to tblUser so I know who issued the MAC Address
IssuedWhen - Date & Time field to know when it was issued

Here's the code I have here at the moment

Private Sub cmdSearch_Click()
 
If IsNull(Me.txtDeviceSerial) Then
    MsgBox "You must enter serial number.", vbOKOnly
Else
    If IsNull(DLookup("[ID]", "tblMACAddress", "[DeviceID] = " & DLookup("[DeviceID]", "tblDevice", "[DeviceSerialNumber]='" & Me.txtDeviceSerial & "'") & "")) Then
        assign = MsgBox("No MAC address assigned to this board, do you wish to assign one now?", vbYesNo)
        If assign = vbYes Then
            'Check device is Ethernet
            If DLookup("[DeviceTypeID]", "tblDevice", "[DeviceID] = " & DLookup("[DeviceID]", "tblDevice", "[DeviceSerialNumber]='" & Me.txtDeviceSerial & "'") & "") = 2 Then
                MsgBox "You can only assign a MAC Address to an Ethernet board. Please enter an Ethernet serial number.", vbOKOnly
            Else
                'Assign MAC Address here
                MsgBox "Assign MAC"
            End If
        End If
    Else
    'Show MAC address here
    MsgBox "MAC Address for Device " & Me.txtDeviceSerial & " is: " & DLookup("[MACAddress]", "tblMACAddress", "[DeviceID] = " & DLookup("[DeviceID]", "tblDevice", "[DeviceSerialNumber]='" & Me.txtDeviceSerial & "'") & "") & "", vbOKOnly
    End If
End If
 
End Sub