Inserting external data stored into a many-many subform combobox

[ChrisNome]

Hi everyone

Here's a dilemma I've been having:

I have a public VBA automation module that holds information in a bunch of temporary variables that I want to import into a master data entry form. It doesn't use recordsets directly manipulating tables because I want the user to be able to see the information on the form and check its accuracy, add things and notes, and undo it before its added to the records if its off.

Here's the thing. It works when adding data to the fields that are controls directly on the data entry form

ex

forms!frmDataEntry![control] = CorrespondingVbaTempVariable

This works great. I can click the undo button if for whatever reason the module messed up, and it clears it

But, frmDataEntry also has a many-many subform, sfrmKeywordsMM, that has a list of all corresponding keywords to search it by.

All sfrm Keywords is is the master form to another subform, sfrmKeywords, which only has a single combo box to input a list of keywords.

All these forms and tables work, that is not the problem. The user inputs as many keywords as they like, if it's already been entered, it autolinks it to the existing keyword, and if it hasn't been entered, the user is prompted if they want to add the keyword and it's added to both the keywords table and the MM table.

The problem is, the VBA module I have has an array called strKeywords() thats set to import a list of keywords into this subform, and i can't for the life of me get it to enter even one.

I've tried the sujested subform notation with just one:
forms!frmdataentry!sfrmKeywordsMM.form![cboKeyword] = "test"
just to test it out and it won't enter anything!

Tried: forms!frmdataentry!sfrmKeywordsMM.form!sfrmKeywords.form![cbokeyword] = strKeyword(0) or "test"

and nothing.

I can get it to set the focus to the combobox [cboKeywords] by:
forms!frmDataEntry!sfrmKeywordsMM.setfocus
but i cannot get anything in that combobox for the life of me. And when it finally does, I have no idea how to loop through that array and put each keyword into the next record (as soon as the user enters a keyword and presses tab, it autocreates the next keyword). I've ever tried that with

for i = 1 to n
forms!frmdataentry!sfrmKeywordsMM.form!sfrmKeywords.form!{cboKeyword] = strkeywords & chr(9)

next

with chr(9) returning a string for the tab key. And this obviously does nothing since I can't even get anything in that combobox to start. Help a brotha out?? Thanks!

CN

 

[ChrisNome]

correction: that strKeywords(i) not strKeywords but you get the point

 

[PWise]

for i = 0 to ubound(strkeywords)
forms!frmdataentry!sfrmKeywordsMM.form!sfrmKeywords.form![cboKeyword].additem = strkeywords(i)

next

 

[PHV]

I'd change the RowSource property of the combo.

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[ChrisNome]

thanks for your responses

PWise: tried your .additem notation without the loop to see if I could get at least one entry added, with both the first value in the array and a test string, and it still doesn't do anything regarding the combo box. Rechecked the wording (ie. [cboKeywords], sfrmkeywords, etc.) and that all seems right. Don't know what's going on and why its so hard to just reference a m-m subform combobox, but apparently it is. So no luck on that one. Got a duplicates error when I first tried it, so that made me excited because it was new, but it appeared that i was trying to import some data i already had and when i tried some brand new data it's the same story, nothing. sfrmKeywordsMM.setfocus sets the focus on the combobox, so i tried a combination of that with your notation, but it still adds nothing to the combo


PHV: i don't know what you mean. the recordsource for the combobox is

SELECT [ID] AS xyz_ID_xyz, [Keyword] AS xyz_DispExpr_xyz, [Keyword] FROM tblKeywords ORDER BY [Keyword];

it does this so the user can reference keywords that are already in the table

the controlsource is keywordIDFK which is the keywordID on the MM table and creates a new MM record there

there is a notinlist event that fires the follow code:

Private Sub cboKeywords_NotInList(NewData As String, Response As Integer)

Dim strSQL As String
Dim i As Integer
Dim Msg As String

'Exit this sub if the combo box is cleared
If NewData = "" Then Exit Sub

Msg = "'" & NewData & "' is not currently in the list." & vbCr & vbCr
Msg = Msg & "Do you want to add it?"

i = MsgBox(Msg, vbQuestion + vbYesNo, "Unknown Keyword...")
If i = vbYes Then
strSQL = "Insert Into tblKeywords ([Keyword]) " & _
"values ('" & NewData & "');"
CurrentDb.Execute strSQL, dbFailOnError
Response = acDataErrAdded
Else
Response = acDataErrContinue
End If
End Sub


..though I don't know why this would not let me input information into the combobox in the first place

... still a >?<

 

[ChrisNome]

wow, that must be a real stumper for all you so called pros

 

[ChrisNome]

alright guys, after a testing out what I could do with the individual subforms I found that the problem is that the combobox is bound by the index (autonumber) but the user input is the seen column, the keywords themselves.

so, for future notice, the vba code...

forms!frmDataEntry!sfrmKeywordsMM.form!sfrmKeywords.form![cboKeywords] = 4...

will correctly input the fourth keyword in tblKeywords into the combobox.

However, the user would input that as a string

So, looks like I'll have to approach this with recordsets afterall. I'll have to see if the keyword is already in the table, select the ID for the record and pass that to the combobox if it's there, and if it's not, insert a new keyword into the table, requery the combobox, and pass that ID to the combobox.

A problem I'm still going to have is I don't understand how to get the combobox to go to the next entry since both are called cboKeywords in design view. PWise: tried that .additem and it said the method wasn't supported

 

[ChrisNome]

hey guys

i solved it using recordsets and it was stupid not to use them since that's why they are there. thanks for your help

CN