Pass combobox to another form always = Null

[MackMan]

Hi Guys. Racking my brains on this one, been at it all day... and just can't figure it out...

I want to pass the Combobox value of one form to a textbox on another using the where condition. Using the break in VBA, the value of the combobox (in this instance "two") shows as null.

Have I missed something?

the control of the combobox is [cboSubCategory] and I want it's value to pass to another form "tblCategoriesSub" (aware of the incorrect reference, as used wizard quickly - but will change if I can get it to work) to the "SubCategory" field.

What have I missed? I'm guessing the use of the single quotes somewhere along the line? All are strings!

 

[pr2-eugin]

You sure you are referencing the right control? The rowsource might be an issue?

 

[MackMan]

Yes. The source is a combobox [cboSubCategory] a subform (but the code is generated on after update of the subform control hence me.cboSubCategory.

The destination is a popup.. frmCategoriesSub (I've NOW changed to frmCategoriesSub) and the field within that popup form is a textbox [SubCategory]

In trying to get this to work, I changed from WHERE to FILTER form, and on the filter part, always remembers the last details... odd?

Anyway, to see what I'm trying to do... the full code is...

Private Sub cboSubCategory_NotInList(NewData As String, Response As Integer)
Dim Message1 As String
      Message1 = "The data you have entered " & Me.cboSubCategory.Text & " is not in the current dataset."
    Const Message2 = "Add now?"
    Const Title = "Unknown entry in SUB CATEGORY Field..."
    Const nl = vbCrLf & vbCrLf
 Dim db As DAO.Database
Dim rs As DAO.Recordset
 If MsgBox(Message1 & nl & Message2, vbQuestion + vbYesNo, Title) = vbYes Then
     Set db = CurrentDb
    Set rs = db.OpenRecordset("tblCategoriesSub")
     With rs
    .AddNew
    .Fields("SubCategory") = NewData
    .Fields("Category") = Me.cboCategory
    .Update
    .Close
 End With
    Response = acDataErrAdded
 Else
    Me.cboSubCategory.Undo
    Response = acDataErrContinue
End If
         DoCmd.OpenForm "frmCategoriesSub", , , [B]"[SubCategory]= '" & Me.cboSubCategory & "'"
[/B]  
 Exit_ErrorHandler:
     Set rs = Nothing
    Set db = Nothing
    Exit Sub
 Err_ErrorHandler:
 MsgBox Err.Description, vbExclamation, "Error #" & Err.Number
Resume Exit_ErrorHandler
 End Sub

Basically, If not in list, Asks me to add, and it does so, perfectly, refreshes the combobox value, and I can continue.

However, I need the popup form to open (modal) to allow me to flow with data integrity elsewhere, before continuing. Opening the popup allows me to do this.

the form opens, but the destination textbox is always blank, OR it always remembers the last value if I close and reopen it..

Many thanks for you help..

 

[MackMan]

Ok, so been having a play with this, and have now managed to pass the value from the subform to the popup form, but.. the only thing is, it's always the previous value.. not this one... so in other words, if I've had to use this method before, it's always the last one I used, not the one I'm currently using??
... I'm guessing I need to clear the filter? but where would I do so?

 

[Galaxiom]

The .Text property is the text currently showing. It only becomes the Value property after it is updated on leaving the control.

 

[MackMan]

Sometimes the answer is right in front of you.

Seeing in this instance I've already updated the table the popup is referring to, I just used the mycombobox.text as the means of filtering it.

Ignorance in this instance is not bliss!...

Thank you for your time!