Kamis, 21 Mei 2020

Re: [MSAccessProfessionals] Form not working as expected

Hi Mercy

You do not need to save the record after you close your "frmfoodcust" form, because the act of closing the form will save the record, if one has been added.

You do need to requery the combo box so that the newly added record is in the list.  However, the correct way to do this is to set the Response argument of the NotInList event, so as to tell Access to perform the requery.  In place of tour DoCmd.DoMenuItem line, add:
        Response = acDataErrAdded

You should also tell Access that you have handled the situation in case the user has clicked "No", otherwise you will get the annoying additional message that the item was not in the list, so before "End If", add:
        Else
                Response = acDataErrContinue

Incidentally, you should never use DoCmd.DoMenuItem, as it is *very* old technology – the "acMenuVer70" in that command line means "execute the menu item from Access V7.0", otherwise known as Access 95, so the method is 25 years old!  A better method is "DoCmd.RunCommand acCmdSaveRecord", or you can simply use "Me.Dirty = False".

However, saving the record at this point will not save the new customer record – it will save the record on the form containing your combobox.  This is probably why your code was looping – it is repeatedly trying to save an incomplete record.

There are also ways to pass the new name the user has entered to the new form, so that the user doesn't have to type it in again.  To help you with this, may I please ask:
- what are the important fields in your customer table?
- what is the RowSource of your combo box?

Best wishes,
Graham

From: MSAccessProfessionals@groups.io <MSAccessProfessionals@groups.io> On Behalf Of M Garcia via groups.io
Sent: Friday, 22 May 2020 11:23
To: msaccessprofessionals@groups.io
Subject: Re: [MSAccessProfessionals] Form not working as expected

 

Ok so I'm back with this same combo box.

 

I took the recommendation of having a separate form to enter the customer information if the customer name is not in the combo box to be selected and there would have to be a requery of the list in the combo box.  Where would this requery go? On the combo box or the form?

 

I tried the following on the combo box:

 

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

 

Dim sMsg As String

 

sMsg = MsgBox("Name not found.  Do you wish to add this new record?", vbYesNo)

If sMsg = vbYes Then

    DoCmd.OpenForm "frmfoodcust", , , , acAdd, acDialog

    DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, , acMenuVer70  ------ is this needed?

End If

 

When the frmcust form opens, I enter the customer name etc and exit the form which brings me back to first form but with the message "Name not found.  Do you wish to add this new record?", vbYesNo and it loops.

 

Is it that I have to clear the contents of the combo box and if yes, where do I put that code?  Is it combo5 = "" ?

 

I'm threading on new ground here.  Forgive me!

 

Regards

Mercy

 

 

 

 

 

 

On Tuesday, May 19, 2020, 05:59:04 AM AST, Graham Mandeno via groups.io <graham=mandeno.com@groups.io> wrote:

 

 

Hi Mercy

I totally agree with Duane here.  How much information do you want to store about a customer?  Just a name and whatever other identifying information appears in the combo box?  I don't think so!  You will maybe want to store postal and delivery addresses, email address, phone numbers, contact names, payment details, and all kinds of other stuff.

It is really easy to add a new record from a NotInList event procedure, but obviously the only data that can be in that record is what the user typed into the combo box, that wasn't in the list.  How is the user going to record all that other data if you don't open a separate form?

Best wishes,
Graham Mandeno [Access MVP 1996-2017]

From: MSAccessProfessionals@groups.io <MSAccessProfessionals@groups.io> On Behalf Of M Garcia via groups.io
Sent: Tuesday, 19 May 2020 07:53
To: MSAccessProfessionals@groups.io
Subject: Re: [MSAccessProfessionals] Form not working as expected

 

Sure!  Thanks for all your assistance.

 

Regards

 

 

 

On Monday, May 18, 2020, 03:03:28 PM AST, Duane Hookom <duanehookom@hotmail.com> wrote:

 

 

IMO, if your user needs to create a new customer then give them a form to do it properly. This isn't a big deal.

 

Duane

 


From: MSAccessProfessionals@groups.io <MSAccessProfessionals@groups.io> on behalf of M Garcia via groups.io <toukey1=yahoo.com@groups.io>
Sent: Monday, May 18, 2020 1:59 PM
To: msaccessprofessionals@groups.io <msaccessprofessionals@groups.io>
Subject: Re: [MSAccessProfessionals] Form not working as expected

 

Understood but trying to avoid the creation/opening of another form to add the customer.

 

I think the LimitToList would work if the combo box was on the custname and not customerid which was suggested in one of your earlier emails. Refer to below for ease of reference.

 

Note I am able to select the customer name fine with the combo box on customerid

 

    

    On Friday, May 8, 2020, 06:39:18 PM GMT-4, Duane Hookom <duanehookom@hotmail.com> wrote:

 

 

    If you are tracking customer visits, then you need a table with at least these fields:

 

    CustomerID

    VisitDate

 

    You would typically add data to this table using a subform on a Customer main form. You could also simply     create a form based on this table with the CustomerID bound to a combo box to select the appropriate     customer.

 

 

Regards

Mercy

 

 

 

My typical strategy is to allow the user to search for the customer in the drop down. If they don't find a record, ask them to click a button to add a new customer. This button would open a customer form in dialog mode for them to create a new customer record. The code following the dialog form opening would requery the combo box and perhaps position the combo box to the most recently added customer.

 

Duane

 

 


From: MSAccessProfessionals@groups.io <MSAccessProfessionals@groups.io> on behalf of M Garcia via groups.io <toukey1=yahoo.com@groups.io>
Sent: Monday, May 18, 2020 1:01 PM
To: MSAccessProfessionals@groups.io <MSAccessProfessionals@groups.io>
Subject: Re: [MSAccessProfessionals] Form not working as expected

 

Yes - the first column which is the CustID field was 0" so I changed it to 1".  After that change and I entered a name in the combo box I got the error "The value you entered isn't valid for this field."

 

 

 

On Monday, May 18, 2020, 01:11:04 PM AST, Paul Baldy <pbaldy@gmail.com> wrote:

 

 

The obvious first question is did you address the issue noted in the error message?  Presuming the bound column is 1, the first column width can't be 0.

Paul

Tidak ada komentar:

Posting Komentar