Results 1 to 8 of 8
  1. #1
    Join Date
    Jul 2003
    Posts
    42

    Unanswered: Append Query - Preventing Blank Entries

    I have a combo on my form which allows users to enter a material no. then upon hitting the button it pulls the data out of a master table from another database into a table in the current db. The problem is the append query does not prevent them from entering a blank entry which nulls out the data in the field. I need to know how to stop it. I tried putting the criteria as follows:

    [Forms]![00_02_03-CRWorksheet]![CurrVend] And Is Not Null

    I also have an update query which will change the existing material no. to a different number.

    Update To: [Forms]![00_02_03-CRWorksheet]![CurrVend]
    Criteria: Is Not Null

    When I try to run these they simply run forever and never stop. Im not sure what to do to correct the problem.

    Any input would be great thanks.

  2. #2
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    could your button_Click event start with

    if isnull(YourComboName.value) then exit sub

    or

    if isnull(YourComboName.value) then
    msgbox "Select something, stupid!", vbInsultUser* , "Operator Error"
    exit sub
    endif

    ( * unfortunately vbInsultUser is not a standard vb constant)

    izy

  3. #3
    Join Date
    Jul 2003
    Posts
    42

    lol!

    Unfortunately I have it currently setup out of vb. I am using a plan ol access macro.

    Ex:

    The first form lets them enter the number to append directly if they know it.

    1st-
    Setwarnings: OFF
    OpenQuery: 00_03-AppendCurrV_Wkst 'This is the append query
    OpenQuery: 00_04-UpdateCurrV_Wkst 'This is the update query
    Setwarnings: On
    Runcommand: Refresh

    00_03 Criteria:
    Field:VID,
    Table:09-VENDORS,
    Append To: VID
    Criteria: [Forms]![00_02_03-CRWorksheet]![CurrVend]

    00_04 Update...has two

    Field:VIDCurrent
    Table:00-CostRed
    Append To: VID
    Criteria: [Forms]![00_02_03-CRWorksheet]![CurrVend]

    &

    Field: CRID
    Table: 00-CostRed
    Criteria[Forms]![00_02_03-CRWorksheet]![CRID]

    .................................................. ..............................................

    The second one takes them to a second form where they can lookup form that looks up the info in the large table and then lets them choose the number that way. I had to do it that way otherwise the form would take too long to load.

    2nd-
    Setwarnings: Off
    OpenQuery: 00_07-AppendCurrV_Lkup 'This is the append query
    OpenQuery: 00_08-UpdateCurrV_Lkup
    Setwarnings: On
    Close: 00_02_05-VIDLookupCurr
    Requery: CurrVen_Subform

    For the most part the queries are the same.

  4. #4
    Join Date
    May 2003
    Location
    UK
    Posts
    233
    Can you set the field the record is updating to Required = Yes. This will then throw up an error message.

    You could then change the error message to "Material number is required"

  5. #5
    Join Date
    Jul 2003
    Posts
    42

    Almost Worked

    This almost worked. It was preventing blank entries by making the field required, however because the data was originally pulled over by an Append query out of someone elses system, for whatever reason I it makes the Append Query stop working. It took me a day to chase down the non-descript validation error message because I hadn't run the query until 3 versions back. So I had to remove the required field in that box and now I am back to square one. Is there any other way to do this, maybe with VB code?

  6. #6
    Join Date
    Aug 2003
    Location
    Delft, The Netherlands (EU)
    Posts
    447

    Re: Almost Worked

    I don't thing that

    [Forms]![00_02_03-CRWorksheet]![CurrVend] And Is Not Null

    is correct.

    I would change the Append Query. You should test, whether your combo value is filled or not, and I guess you should do that by comparing with the empty string vbNullString. If you don't have an user input, take a default value. Looks like:

    IIf([Forms]![00_02_03-CRWorksheet]![CurrVend] = vbNullString, <default value>, [Forms]![00_02_03-CRWorksheet]![CurrVend])
    Make everything as simple as possible, but not simpler! - A. Einstein
    DB Problems? DB Explorer, BTrieve Re-engineering, DB Conversions & ETL? Conversion Tool

  7. #7
    Join Date
    Jul 2003
    Posts
    42

    Default Value

    By default value...do you mean a standard value I pick out? If that is what you mean, then there really isnt one I can default to because this data is pulled from our SAP system. I think we are getting closer...is there a way to put the append in a code with an message/error box or just plain not allow the append query to run at all on click if the unbound combo box is null?

    Thank you for your help..=)

  8. #8
    Join Date
    Aug 2003
    Location
    Delft, The Netherlands (EU)
    Posts
    447

    Re: Default Value

    I feel you are mixing two things:

    For you (bulk) import from SAP, you really should define a default value, maybe a value which does not yet exists

    For you end-user application, you should indeed only execute your append if you have a valid value, and to come up with a msgbox as shown earlier in this thread otherwise.
    Make everything as simple as possible, but not simpler! - A. Einstein
    DB Problems? DB Explorer, BTrieve Re-engineering, DB Conversions & ETL? Conversion Tool

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •