New data and combo box

[Ela]

Hi,
I was wondering if anyone can help me.

I have a main form with a combo box (it shows Users LastName) runing on a query. Here is the code:
---------------------------------------------
SELECT DISTINCTROW Users.UserID,Users.LastName FROM Users ORDER BY Users.LastName;
---------------------------------------------

Additionaly on the same (main) form I have a button, which opens the subform 'Users' so I can edit the LastName of the 'user'. Here is the code (in on Click event) for the button:

---------------------------------------------
Private Sub CmdUser_Click()
On Error GoTo Err_CmdUser_Click

Dim stDocName As String
Dim stLinkCriteria As String
stDocName = "Users"
stLinkCriteria = "[UserID]=" & Me![UserID]
DoCmd.OpenForm stDocName, , , stLinkCriteria

Exit_CmdUser_Click:
Exit Sub

Err_CmdUser_Click:
MsgBox Err.Description
Resume Exit_CmdUser_Click

End Sub
-------------------------------------------

It works fine for existing 'user' but I have a problem adding new 'user' in a certain situation:
Let imagine I have to add a new record on the main form and the combo box field is empty (O.K.). Now I have to put a 'user' Lastname for that new record. I don't now if the 'user' is in my database or not, so I go to the combo box and start typing the 'Lastname' in the combo box field.
The name is not on the list so I delete what I have typed and try to open the subform 'Users' (button) to add a NEW 'user' Lastname, but the button is locked and I get msgbox:

"Syntax error (missing operator) in query expression '[UserID]=' ".

Thanks for any advice,
E.

 

[chrismcbride]

Ela

You need to condition the call to the Users form so that the null value of an empty combo box is not passed as a open parameter to the DoCmd.OpenForm method.

---------------------------------
Private Sub CmdUser_Click()
On Error GoTo Err_CmdUser_Click

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "Users"
stLinkCriteria = "[UserID]=" & Me![UserID]

If Me.UserID > 0 then
DoCmd.OpenForm stDocName, , , stLinkCriteria
else
DoCmd.OpenForm stDocName
end if

Exit_CmdUser_Click:
Exit Sub

Err_CmdUser_Click:
MsgBox Err.Description
Resume Exit_CmdUser_Click

End Sub

-----------------------------------

In the case that there is no value in the combo box the form will open to the first record with all records in the base table included. If you want you can add this...

DoCmd.SelectObject acForm, stDocName
DoCmd.GoToRecord acDataForm, stDocName, acNewRec


... right after the doCmd.openform and the form will open to a new record.

HTH
Chris

 

[Ela]

Hi Chris,
Thank you for your advice.

It works great, but I realized that after I close the 'User' subform the value in the combo box is the first record and not the new record that I have typed.

In the 'Users' subform in the on close event I have add :

------------------------------------------
Private Sub Form_Close()

Forms.Item("MainForm").Controls("UserID") = Me.UserID

End Sub
------------------------------------------

so after closing the subform the new record
appear in the selected text part of the combo box. Now it don't work.

Any idea what I'm doing wrong?

Thanks,
E.

 

[Ela]

Hi,
Chris or anyone else could help me with the above problem? Please ....

Thank you in advance
E.

 

[deadman]

Hello Ela,

I'm using Access2K. Don't know if this will help, but what I have done when the item that I need is not in the list is have the subform open by using the NotOnList event. Here is the code that I am using...

Private Sub cboItemname_NotInList(NewData As String, Response As Integer)
Dim Result
Dim Msg As String
Dim CR As String
CR = Chr$(13)
If NewData = "" Then Exit Sub
Msg = "The Grocery Item '" & NewData & "' is not in the Grocery Items list." & CR & CR
Msg = Msg & "Do you want to add it?"
If MsgBox(Msg, vbQuestion + vbYesNo) = vbYes Then
DoCmd.OpenForm "frmItems", , , , acAdd, acDialog, NewData

End If

Result = DLookup("[itemname]", "tblItems", "[itemname]='" & NewData & "'")
If IsNull(Result) Then
Response = acDataErrContinue
MsgBox "Please select an Item from the list!"
Else
Response = acDataErrAdded
End If
End Sub

Then to make the new data or item available on the original combo box, I have the OnGotFocus event for the combo box with the following code...

Private Sub cboItemname_GotFocus()
On Error GoTo Err_cboItemname_GotFocus

DoCmd.DoMenuItem acFormBar, acRecordsMenu, 5, , acMenuVer70

Exit_cboItemname_GotFocus:
Exit Sub

Err_cboItemname_GotFocus:
MsgBox Err.Description
Resume Exit_cboItemname_GotFocus

End Sub

Hope this helps.

Mike

 

[Ela]

Thank you Scott.
I need some time to think about your solution.

Regards,
E.