Combo Box

[Mahendra1000]

Hello All,



First of all I would like to thank for your Attention in this post.

I have a Main Combo box linked to column from a table. The Content of column consists of FirstName, LastName, MiddleName. There are Five Combo boxes below this Main Combo box. These five Combo boxes are automatically filled based on the value selected in Main Combo box.

There is an Option to enter new value into Main Combo box. Whenever the frontend users enter new value into Main Combo box then Access prompting a Dialog box stating that "The value you entered is not valid for this field" with an Options of end and debug. Then the user needs to select an "end" Option to manually select data from the three Combo boxes..

My questions

1) Is there any Option to get rid of this Dialog box from Access as the front end users should not see the debug Option. When the front end user enters the new values then they should manually select those five Combo boxes with out any interruption. I have this code in AfterUpdate Event

Private Sub cbxAuftraggeber_FK_AfterUpdate()
Dim rsAuftraggeber As Recordset
Dim sSQL As String
sSQL = "SELECT AuftrG_Fuhrungsebene, BU, [AuftrG_A1], [AuftrG_A2], [AuftrG_A3], AuftrG_Kst FROM TBL_AS_DATA WHERE [Auftraggeber FK] ='" & Me.cbxAuftraggeber_FK.Value & "'"
If sSQL = "" Or IsNull(sSQL) Then
    Exit Sub
End If
Set rsAuftraggeber = CurrentDb.OpenRecordset(sSQL)
Me.cbxFuhrungsebene.Value = rsAuftraggeber.Fields("AuftrG_Fuhrungsebene")
Me.cbxBu.Value = rsAuftraggeber.Fields("BU")
Me.cbxA1.Value = rsAuftraggeber.Fields("AuftrG_A1")
Me.cbxA2.Value = rsAuftraggeber.Fields("AuftrG_A2")
Me.cbxA3.Value = rsAuftraggeber.Fields("AuftrG_A3")
Me.cbxKstStelle.Value = rsAuftraggeber.Fields("AuftrG_Kst")
End Sub

I don't know much about coding in VBA. could you guide me how to overcome my issue.

2) There is a search funtion for the Main Combo box to search the data.. But the Problem with it is it searches only in the firstname but not in lastname and middlename.

For example: If user enters Ma it display only the results that starts with MA* but not with *MA*. I Need it like *MA*

I tried it by making a query and running a macro attached to this query in on Enter/Change event but it didn't worked. Hoping for a postive replies from your end.

[ranman256]

after the last DIM statement, (1st line of code)
put

Dim sSQL As String


on error resume next

this will stop the code errors.
But you should not be using vb code for any of this.
A user should select item from the combo, then you would run a query, or a filter. NOT use vb code to fill in data. Esp if you dont know vba.
Access can be programmed without the need for lots of vba or knowing vba.
It can be run completely on macros & queries.

[Mahendra1000]

Worked like a champ!!

Thanks a ton ranman256 and for your suggestions too

could you know how to integrate a search function to that (2nd question in my post). No worries if this won't work..... I mainly concerned with the 1st question and it got solved

[ranman256]

If you want to seach data in a form,
You cant use form boxes in a query if there's nothing in them..so..
Test all controls for a possible filter then build the where clause.

if not isnull(cboState) then   sWhere = sWhere & " and [state]='" & cboState & "'"
if not IsNull(txtName) then    sWhere = sWhere & " and [Name]='" & txtName & "'"
if not IsNull(chkContact) then sWhere = sWhere & " and [Contact]=" & chkContact.value


    'remove 1st And
if len(sWhere)>0 then sWhere= mid(sWhere,5)


  'just use the filter
iLen = Len(sWhere) - 5
If iLen <= 0 Then
    me.filterOn = false
Else
    me.filter = sWhere
    me.filterOn = true
End If

'OR
'apply the sql to the form
sSql = "SELECT * FROM tblCompany WHERE " & sWhere