Setting Field to SQL query Answer, and Updating Same 2

[ChrisCalvert]

Hello, this is long and involved, so bear with me....

I have two tables tblLogins and tblEmployeeData. tblLogins has fields LoginID (a number used to login to phone system, this is a limited number we have, it is the key field of this table) and EmpNo (employee number). tblEmployeeData has several fields but I am only concerned with EmpNo (same number as above). I have two forms, frmNewAgent and frmBrowse, both forms based on tblEmployeeData. I need to have a button on frmNewAgent that assigns the next availible (the EmpNo field is blank) LoginID from the tblLogins to this employee by writing the EmpNo (from the current record on the tblEmployeeData table) to that null EmpNo field (on the tblLogins). That way I can assign the new employee one of the availible logins that is not assigned to any other employee. It was suggested to me to use Max() in the query and I think I have the query correct, but I am unsure of how to implement this. I want it to be as simple as possible (it does not seem very simple at this point). I have the following query

UPDATE tblLogins SET tblLogins.EmpNo = ??Not Sure, I think Me!EmpNo?? WHERE (((tblLogins.EmpNo)=(SELECT max(LoginID) from tblLogins where isnull(tblLogins.LoginID))));

But I don't really know what to do with the query. Some how I need to make this execute and update the tblLogins.EmpNo.

ALSO: I have an unbound textbox on the frmBrowse that I want to query the tblLogins and match the LoginID to the EmpNo on the form. (As opposed to copying the assigned LoginID to a field in the tblEmployeeData, I just want to query off the tblLogins) I have the control set to :
=SELECT tblLogins.LoginID
FROM tblLogins
WHERE (((tblLogins.EmpNo)=[Forms]![frmBrowse]![EmpNo]));

But it tells me there is a problem with the syntax of the subquery (WHAT subquery?) and to place parenthesis around the subquery.

Can anyone help with either of these issues? I would greatly appreciate it.


-chris

 

[WayneRyan]

Chris,

Hope this will get you started.

On your frmNewAgent place a command
button labeled "Add" or something.
In its "On-Click" event put the
following code.



' *****************************************
Dim dbs as Database
Dim rst as Recordset
Dim strSQL as String
Dim Empno as Long
Dim LoginID as Long


Set dbs = CurrentDb

'
' Get the LoginID and enter
' employee's EmpNo
'
strSQL = "SELECT max(LoginID) " & _
"from tblLogins " & _
"where isnull(LoginID);"
set rst = dbs.OpenRecordSet(strSQL)
If rst.EOF and rst.BOF Then
MsgBox("There are no available Login IDs&quot
Exit Sub
Else
LoginID = rst!LoginID
rst.edit
rst!EmpNo = Me.EmpNo
rst.update
End If
set rst = Nothing
'
' Any updates of EmpTable, etc...
'
set dbs = Nothing

' *****************************************

I didn't the above, but it should give you
a start.

Also, you can do the following:
'
' This is from your post, but I think
' the logic is wrong
'
strSQL = "UPDATE tblLogins " & _
"SET tblLogins.EmpNo = " & Me.EmpNo & " "
"WHERE EmpNo In " & _
"(SELECT max(LoginID) " & _
" from tblLogins " & _
" where isnull(LoginID);"
dbs.execute(strSQL)


HTH,
Wayne

 

[vb6novice]

Chris,

If you want a code solution, add your button to assign a LoginID on frmNewAgent and have it run the following procedure:

Private Sub get_available_login_id()
'this code opens a recordset of the LoginID table
'locates the first record with null EmpNo field and
'writes the current EmpNo from the text box on the
'New Agent form to the record
'Then writes the LoginID to a text box on the form (assuming there is one)

Dim dbs As Database
Dim login_rst As Recordset
Dim id_available As Integer

id_available = 0
Set dbs = CurrentDb
Set login_rst = dbs.OpenRecordset("tblLogins&quot
Do Until login_rst.EOF = True
If IsNull(login_rst![EmpNo]) = False Then
login_rst.MoveNext
Else
login_rst.Edit
login_rst![EmpNo] = Me![txtEmpNo] 'or whatever is the name of your EmpNo text field on frmNewAgent
login_rst.Update
'me![txtLoginID] = login_rst![LoginID] 'remove leading comment mark only if you have such a text field on frmNewAgent
login_rst.Close
dbs.Close
id_available = 1
Exit Do
Loop
login_rst.Close
dbs.Close

If id_available = 0 Then
MsgBox "There are no more Login ID's available."
End If

End Sub

This code will not prevent you from assigning a LoginID more than once. I recommend you make the button Enabled = No, and only change it to Yes when there is no value of LoginID for the current tblEmployee record. As soon as you assign a LoginID, you should disable the button.

How do you get rid of EmpNo from the tblLoginID when an employee no longer needs one? The EmpNo field needs to be NULL in order for this code to work. Ensure that when LoginID's are made available again the EmpNo values in the table for those ID's are NULL (not "&quot.

 

[ChrisCalvert]

Thanks to both of you.

I ended up using a (slightly modifired) version of your code vb6novice.

One question, to have a textbox on the frmBrowse show the employees login, would I have to declare a recordset and loop through, looking for the EmpNo match? Is there a simpler way to do that, since I am only looking for a value and not seeking to update/change anything?

Thanks again,

Chris