combo box clearing after entry

[Pat]

I start a form in Add mode, initially a combo box is locked. The on current event fires up and unlocks the combo box amongst other things.
When I select an item from the combo box, it clears the combo box and goes to the BeforeInsert event.

When I click on another control it goes to the AfterUpdate event of the combo box where all reference to the combo box and its columns are all Null.

When i try to select another item from the combo box, it shows the list but when i click on an item, it leaves the combo box list in the dropdown state and won't allow me to select it.

What am i missing here?

[HansV]

If you can drop down the list but not select anything from it, this usually indicates that the combo box is locked. Does the form contain code that locks the combo box again?

Without knowing more details, I have no idea why selecting an item from the list would clear the combo box.

[Pat]

It is not locked after this series of events.
Do I need to have a before update event for the combo box? Boy am I clutching at straws.
I notice that Data Entry is true, does this have any bearing?

[HansV]

Data Entry = Yes means that the form is in Add mode, i.e. you can only create new records, not view/edit existing records. In itself, that does not explain the behavior you describe.

Could you post the code behind the form, or attach a stripped down, compacted and zipped copy of the database?

[Pat]

I can post the code, but the database is 24Mb.

Sorry hans, the field in question is Equipment.

[HansV]

What is the name of the combo box you mentioned in the first post?

[Pat]

The combo box is Equipment, sorry about that

[HansV]

That's a lot of code. The only thing that caught my eye is this:

Code: Select all

Private Sub Equipment_BeforeUpdate(Cancel As Integer)
    Equipment_AfterUpdate
End Sub

Generally, it's not a good idea to call AfterUpdate code in the BeforeUpdate event; this can confuse Access. What happens if you comment out the BeforeUpdate code?

[Pat]

That was just put in just prior to my last test, so you can ignore that.

Does the BefoeInsert event stuff things up?

[Pat]

I just copied the form to another name to play around with and it came up with an error while copying, i have included a snapshot of the error message.

Could this be causing a problem?

[HansV]

Does the Befo(r)eInsert event stuff things up?

Not as far as I can see. Is the Equipment field updateable in the record source (table or query) of the form?

[HansV]

I just copied the form to another name to play around with and it came up with an error while copying

Does the form contain controls that are not native to Access, for example a DateTimePicker control?

[Pat]

Does the Befo(r)eInsert event stuff things up?

Not as far as I can see. Is the Equipment field updateable in the record source (table or query) of the form?

It's a normal query, I include it here.
SELECT ServiceJobs.JobLink, ServiceJobs.DateBookedIn, ServiceJobs.DateRequired, ServiceJobs.TimeBookedIn, ServiceJobs.TimeRequired, ServiceJobs.Priority, ServiceJobs.Warranty, ServiceJobs.WarrantyChargeTo, ServiceJobs.FaultDescription, ServiceJobs.AccessoriesProvided, ServiceJobs.OfficeComments, ServiceJobs.JobStatus, ServiceJobs.Completed, ServiceJobs.DateCompleted, ServiceJobs.Equipment, ServiceJobs.QuoteDescription, ServiceJobs.QuoteAmount, ServiceJobs.RepairProcedure, ServiceJobs.Notes, ServiceJobs.ServiceID, ServiceJobs.JobTotalCostGST, ServiceJobs.Jobtotalcost, ServiceJobs.WaitingonParts, ServiceJobs.Suspended, ServiceJobs.PaymentMethod, ServiceJobs.Printed, ServiceJobs.sAddress, ServiceJobs.sAddress2, ServiceJobs.ssuburb, ServiceJobs.sState, ServiceJobs.sPostCode, ServiceJobs.ShopToClient, ServiceJobs.ShppingInstructions, ServiceJobs.ServiceResult, ServiceJobs.NextService, ServiceJobs.ServiceInternal, ServiceJobs.StorageLocation, ServiceJobs.masterjob, ServiceJobs.ExternalQuote, ServiceJobs.ExternalRepairProcedure, ServiceJobs.ExternalCalibrationProcedure, ServiceJobs.jobsinbatch, ServiceJobs.followupdate, ServiceJobs.ServiceRequired, ServiceJobs.Dispatched, ServiceJobs.Log, ServiceJobs.Quote, ServiceJobs.Department, ServiceJobs.ForExport, ServiceJobs.Location, ServiceJobs.jobemail, ServiceJobs.confirmedbyemail, ServiceJobs.datedispatched, ServiceJobs.phonenumber AS jphonenumber, ServiceJobs.phonenumber2 AS jphonenumber2, ServiceJobs.[fax number] AS [jfax number], ServiceJobs.[mobile phone] AS [jmobile phone], ServiceJobs.[email address] AS [jemail address], ServiceJobs.shippingnotes AS jshippingnotes, ServiceJobs.documents, ServiceJobs.StatusChanged, ServiceJobs.QRepair, ServiceJobs.QCalibration, ServiceJobs.QParts, ServiceJobs.QFreightCol, ServiceJobs.QFreightRet, ServiceJobs.QOther, ServiceJobs.QSurveyFee, ServiceJobs.QTime, ServiceJobs.QuoteConfirmed, ServiceJobs.LabourCharge, ServiceJobs.Paused, ServiceJobs.LoggedOnBy, ServiceJobs.LoggedOn, ServiceJobs.PendingDelivery, ServiceJobs.DispatchNumber, ServiceJobs.ExtNotesCustomerCanView, ServiceJobs.QuoteCustomerCanView, ServiceJobs.QEquipment, ServiceJobs.QComments, ServiceJobs.Loading, ServiceJobs.LoadingTime, ServiceJobs.BookedOn, ServiceJobs.SubContractor, ServiceJobs.SubContractorOrderNo, ServiceJobs.SubContractorFreightTo, ServiceJobs.SubContractorFreightFrom, ServiceJobs.SubContractorCharges, ServiceJobs.SubContractorCurrency, ServiceJobs.SubContractorMargin, ServiceJobs.SubContractorTotal, ServiceJobs.ServiceCheck, ServiceJobs.Specification, ServiceJobs.contractLink, ServiceJobs.showCalibrateSupply
FROM (([Job Costing] INNER JOIN ServiceJobs ON [Job Costing].ID = ServiceJobs.JobLink) LEFT JOIN ClientBase ON ServiceJobs.ShopToClient = ClientBase.CustomerNumber) INNER JOIN ClientBase AS ClientBase_1 ON [Job Costing].CustomerNumber = ClientBase_1.CustomerNumber;

[Pat]

I was wrong about comctl, it was a CommonDialog control.

[HansV]

It's a normal query, I include it here.
SELECT ... ServiceJobs.Equipment, ...

If you open this query directly, can you edit the Equipment field?

[HansV]

I was wrong about comctl, it was a CommonDialog control.

The common dialog control is part of VB6, it is not installed on every PC.

For most purposes, you can use the built-in Application.FileDialog object instead of the common dialog control; you'd have to rewrite the code however.

[Pat]

It's a normal query, I include it here.
SELECT ... ServiceJobs.Equipment, ...

If you open this query directly, can you edit the Equipment field?

Yes i can edit that field, i can also add a record in the query too.

[Pat]

I was wrong about comctl, it was a CommonDialog control.

The common dialog control is part of VB6, it is not installed on every PC.

For most purposes, you can use the built-in Application.FileDialog object instead of the common dialog control; you'd have to rewrite the code however.

Can you steer me in the direction of the code for the Application.FileDialog .

[HansV]

Yes i can edit that field, i can also add a record in the query too.

I am very sorry, but I fear I can't solve the problem of the combo box for you.

[HansV]

Can you steer me in the direction of the code for the Application.FileDialog .

Here's an example of using FileDialog to let the user select a text file:

Code: Select all

  Dim strFile As String
  With Application.FileDialog(msoFileDialogOpen)
    .Filters.Clear
    .Filters.Add "Text files", "*.txt"
    If .Show = True Then
      strFile = .SelectedItems(1)
    Else
      MsgBox "No file selected!", vbExclamation
      Exit Sub
    End If
  End With
  ' Code to do something with the file name goes here
  ...

And an example to let the user select a folder:

Code: Select all

  Dim strFolder As String
  With Application.FileDialog(msoFileDialogFolderPicker)
    If .Show = True Then
      strFolder = .SelectedItems(1)
    Else
      MsgBox "No folder selected!", vbExclamation
      Exit Sub
    End If
  End With
  ' Code to do something with the folder name goes here
  MsgBox strFolder

The code needs a reference to the Microsoft Office n.0 Object Library (where n = 11 for Office 2003, n = 12 for Office 2007 and n = 14 for Office 2010). Chances are that you already have this reference or that Access will offer to set it for you if not.