Combo Box Problems

[bflemi3]

I have a subform. The porpose of the subform is to be able to select multiple choices from a combo box in a continuous form view. I have seen it done in example databases...but i can't get it to work in mine.

I created three tables:
1) TblContract - This is the source for the main form. 3 fields - Division, ProgramName, ContractNumber (Primary Key);

2) TblRequirements - a Requirement ID (ReqID (Primary Key) - this is auto number) and a field with all of the different requirements (Requirement).

3)TblContractReq - ReqID, Requirement, ContractNumber. No primary Key.

I created a many to many relationship. The combo box pulls the Requirement field from TblRequirements.

Right now, in contiunuous form view, the subform shows 8 combo box's with each seperate requirement listed. If yuo try to change it it says "The value you entered isn't valid for this field. For example, you may have entered text in a numeric field or a number that is larger then the fieldsize setting permits."

Please Help..thank you in advance.
-B

 

[tails]

Sounds like you need to create one single column that is on every table. Eg maybe a number or some sort. Then you can create a relationship between each of the number fields on each of the tables so it has something to link to. This may allow it to read it better without the confusion of the many to many relationship. I'm not sure if this will work but its probably worth a shot.

 

[bflemi3]

I was able to make it work by taking code from another example database...only problem is now that when I try to open the subform it comes up with a box that says Enter Parameter Value for tblContractRequirement.ContractID. Why would this happen?

 

[tails]

looks like its trying to get some of the data it needs to run the form. When you deleted some of the code did you get rid of anything that allowed or made it gather data from the database?

 

[Andrew Fergus]

Following on from the suggestion from tails, check the fields on the subform have the correct control source and also if any of the fields refer to a query, check the query is also using the correct table / form / field names.
HTH, Andrew

 

[bflemi3]

Tails, I took the exact code from the example database. The example did the exact thing that i need to do. i used the code that they had in the control source and row source for the combo box as well as the for the sub form.

Andrew, the combo box pulls from a table. The table has ReqID (PK, autonumber) and Requirements (text).

Here is the code that i am currently using:

Combo Box Control Source: ReqID (from tblRequirements)

cbo Row Source: SELECT DISTINCTROW tblRequirements.ReqID, tblRequirements.Requirements FROM tblRequirements ORDER BY tblRequirements.Requirements;

Subform Record Source: SELECT DISTINCTROW tbContractRequirements.ContractID, tblContractRequirements.ReqID, tblRequirements.Requirements FROM tblContractRequirements INNER JOIN tblRequirements ON tblContractRequirements.ReqID=tblRequirements.ReqID ORDER BY tblContractRequirements.ContractID;

Thanks for the help

 

[bflemi3]

I figured out that the code i was using was to display data, not enter data, which is what i need to do. I'm looking for it now...any suggestions?

 

[bflemi3]

Finally!!! I got it to work.

I also took code for the NotInList event, but it came up with a compile error: User-defined type not defined. It references a declaration: dbs As DAO.Database

Im new to this so I have no clue what this means. Here is the code...any guesses??

Private Sub cboRequirements_NotInList(NewData As String, Response As Integer)
'Set Limit to List property to Yes

On Error GoTo ErrorHandler

Dim intResult As Integer
Dim strTitle As String
Dim intMsgDialog As Integer
Dim strMsg1 As String
Dim strMsg2 As String
Dim strMsg As String
Dim cbo As Access.ComboBox
Dim dbs As DAO.Database
Dim rst As DAO.Recordset
Dim strTable As String
Dim strEntry As String
Dim strFieldName As String

'The name of the look-up table
strTable = "tblRequirements"

'The type of item to add to the table
strEntry = "Requirement"

'The field in the look-up table where the new entry is stored
strFieldName = "Requirements"

'The add-to combo box
Set cbo = Me![cboRequirements]

'Display a message box asking if the user wants to add
'a new entry
strTitle = strEntry & " not in list"
intMsgDialog = vbYesNo + vbExclamation + vbDefaultButton1
strMsg1 = "Do you want to add "
strMsg2 = & strNewData & " as a new " & strEntry & "?"
strMsg = strMsg1 + strNewData + strMsg2
intResult = MsgBox(strMsg, intMsgDialog, strTitle)

If intResult = vbNo Then
'Cancel adding the new entry to the look-up table
intResponse = acDataErrContinue
cbo.Undo
Exit Sub
ElseIf intResult = vbYes Then
'Add a new record to the look-up table
Set dbs = CurrentDb
Set rst = dbs.OpenRecordset(strTable)
rst.AddNew
rst(strFieldName) = strNewData
rst.Update
rst.Close

'Continue without displaying default error message
intResponse = acDataErrAdded

End If

ErrorHandlerExit:
Exit Sub

ErrorHandler:
MsgBox "Error No: " & Err.Number & "; Description: " & Err.Description
Resume ErrorHandlerExit

End Sub

 

[Norie]

Generally it is a good idea to type variables/objects when you declare them.

But in this case you should get away without doing that.
Replace

Dim dbs As DAO.Database 
Dim rst As DAO.Recordset

with just this

Dim dbs
Dim rst

If you really want to type them you'll need to set a reference to the latest Miscrosoft DAO library under Tools>References...