Replies

Shane,

You are right, there are a lot of John Smith’s running around. grin. I visually verified that there are no “duplicate” names in my current table. Since the tblParticipant is pretty much done (We don’t get that many new additions lately), I think it would be safe enough to do so.

I’ll try that idea of yours. I had the same idea, tried it out and it still accepted the entry. Although, I had done it via table instead of a form.

Sure.

This is from frmAcceptance – Name combo box.

SELECT [tblParticipant].[ParticipantID], [LastName] & “, ” & [FirstName] AS Expr1, [tblAppStatus].[AppStatus] FROM tblParticipant INNER JOIN tblAppStatus ON [tblParticipant].[ParticipantID]=[tblAppStatus].[ParticipantID] WHERE ((([tblAppStatus].[AppStatus])=2));

This is the row source for the frmSetAppStatus Name combo box:

SELECT [tblParticipant].[ParticipantID], [LastName] & “, ” & [FirstName] AS Expr1, [tblParticipant].[LastName] FROM tblParticipant ORDER BY [tblParticipant].[LastName];

Got an error message saying that use of null value is not allowed. Oh well.

Thanks for your help, I guess it is simply not possible. I’ll have the user enter the name twice (once in main form and once in the fldg).

Ahhh…Please forgive me for being such a thick-head grin.

So, in the NotInList event, after DoCmd.OpenForm…line, I add this line: Me.Name.Column(0)?

Brent

How can I pass the ID if it isn’t in the list?

Guess it is simply not possible. I’ll have to bite the bullet and force users to type the name again in secondary form.

The columns in the Unbound Combo Box are:

ParticipantID
Name: LastName&”, “&FirstName

The name is being passed on to the combo box via VBA (I can verify it by “hovering” my cursor and getting a tool tip telling me the results) but it is NOT showing up in the combo box field. I am sorry for not being clear in my previous post.

I’m using Access 2000

I’ll test the code, you have done a lot by giving me suggestions, I don’t want to impose on you too much. Smile.

Thanks for the clarification, I tried your code, at the break points, I can see that the name of participant is being passed on but still refuse to show up in the dialog box. I’m gonna look up in my reference book because I recall seeing a similiar situation that worked before…

The reason there is a combo box on the second form is because I was trying to filter my main form combo box to show only participants who were accepted to camp. For reasons too lengthy to list here, I couldn’t use conventional filtering so I had to set the combo box to list participants based on their Application Status (Waiting List, Accepted and Not Accepted). If the person is still on Waiting List, then that person wouldn’t show up on the combo box. This is where my code fires and opens second form which enables me to look up that person and change the status and then requeries the main form so that the person would show up there.

1. Does the code you just posted work with combo boxes?
2. I don’t understand your code. Which parts of it that I need to switch with names of my forms/fields?

Thanks for your time and patience! I’m learning a lot from this.

I can’t seem to attach it…

Here’s the code:

Private Sub cboName_NotInList(NewData As String, Response As Integer)
‘ If not listed, then open fldgSetAppStatus

Dim strTitle As String
Dim intMsgDialog As Integer
Dim strMsg1 As String
Dim strMsg2 As String
Dim strMsg As String
Dim strEntry As String
Dim strFormName As String
Dim frm As Form
Dim intReturn As Integer
Dim strName As String

strFormName = “frmAcceptance”
strEntry = “ParticipantID”
Set frm = Forms(strFormName)

‘Display a message box asking if the user wants to add
‘a new entry
strTitle = strEntry & ” Not in List”
intMsgDialog = vbOK + vbExclamation + vbDefaultButton1
strMsg1 = “You need to set ”
strMsg2 = “‘s App Status.”
strMsg = strMsg1 + NewData + strMsg2
intReturn = MsgBox(strMsg, intMsgDialog, strTitle)

If intReturn = vbCancel Then
Response = acDataErrContinue
frm.Undo
Exit Sub
ElseIf intReturn = vbOK Then
‘Open form for adding new client
frm.Undo
Response = acDataErrContinue
DoCmd.OpenForm “fldgSetAppStatus”
Forms!fldgSetAppStatus!cboName = NewData
End If

End Sub

We are getting there. When I set the breakpoint in the VB editor, and I see it says: “cboName=Traub, Carolyn” but the dialog box still shows the Name combo box as blank. Am I missing something?

This is what I did: Forms!fldgSetAppStatus!cboName=NewData and this is the last line in the code. Does the order of code affect this somehow?

Paul, thanks for the tip. Now, I’m not sure how do I write a code that pastes NewData into the unbound combo box on the dialog box?

I’ve tried Mid statement (can see the NewData content) but how do I tell it to actually insert it into the combo box?

Thanks to you both for your suggestions. I had finally figured out why it wasn’t working (I’m still relatively new to VBA).

I had attempted to open a dialog box whenever the item isn’t in the list in the main form. I belatedly realized that if the item wasn’t in the list the first time around, then I wouldn’t be able to set the dialog box combo box to match the main form (no key).

If you have an idea of how I could somehow parse the name in the main form’s combo box into the dialog box, that’d be great.

Again, thanks in advance for your help.

Man! You are a KNIGHT IN A SHINING ARMOR!

If you ever are in Seattle area, give me a ring and I’ll treat you to a dinner! Smile.

By the way, how do you know all of this arcane stuff? The book never mentioned this nor did the Microsoft support website or help files.

I am not sure I understood the thread correctly, but I work with visually impaired people and they cannot see checkboxes, I was forced to use combo boxes so that they could see “Yes”/”No”. Then, I stumbled into this one:

In table: Set those fields you want to be Yes/No, then in the Properties for those Yes/No fields, click on Lookup tab and change the view to Text.

Go to your forms and delete the checkboxes and add them again. Access will automatically change them to textboxes and all you need to do is to type “yes” or “no” and it will automatically store -1 (yes) or 0 (no) in the table.