Page 2 of 4 FirstFirst 1234 LastLast
Results 16 to 30 of 48
  1. #16
    ssanfu is offline Master of Nothing
    Windows XP Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    I hadn't even considered a lookup field.

    However, one image shows that "NType" in the table design is a number (didn't see a field named "ID"), so maybe


    Code:
    Private Sub cboNType_Afterupdate()
            Me.ncrnumber = Nz(DMax("[NCRNumber]", "tblNCR", "[NType]=" & cboNType), 0) + 1
    End Sub

  2. #17
    raychow22 is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    May 2017
    Posts
    145
    QC checklist R1 Final_Backup 6-8-17.zipQC checklist R1 Final_Backup 6-8-17.zip

    I've tried everything you guys suggested. Thanks! Ive attached my db here. Can you guys take a look. Also take a look at my other tables and forms and suggest any improvements on them. Thanks!!

  3. #18
    ssanfu is offline Master of Nothing
    Windows XP Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    Can't open the Zip file.......
    EDIT: Correction...I couldn't extract the accdb file from the Zip file!

    Create the Zip file, then try and extract it to a different folder before uploading......

  4. #19
    raychow22 is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    May 2017
    Posts
    145
    Strange.

    I opened the zip file. It's fine for me.
    Attached Files Attached Files

  5. #20
    orange's Avatar
    orange is online now Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    Ray,

    I looked at your database briefly.
    I would recommend against the use of
    -lookup fields at the table level
    -embedded spaces and special characters {eg Reference Document(s) } in field and/or object names
    -words reserved by MS Access (Description, Type...)

    You have 16 tables but only 2 are related in your relationships window??
    Table tblSpecifications has fields with names Field3 thru Field29??
    Some tables are not Normalized.

    I realize this doesn't answer your specific question, but I don't know your specific requirements.

  6. #21
    ssanfu is offline Master of Nothing
    Windows XP Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    Add to that, I wouldn't /don't use multi-value fields. They look nice on a form and easy for data entry, but they are a real PITA trying to get a printout (IMO - YMMV)).

  7. #22
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    OK, I can open the db, but no idea which form is causing the problem. The one with the same title (Form Nonconforming Reports) is called formNCR - in your images above it is called tblNCR1. And it does not have the cboNType control you refer to.

    Also, you need the maximum ncr value, but your lookup is looking in tblNCR - which has the ncrnumber formatted as 'NCR'000 so cannot be anything else but an ncr number so the criteria element of your dmax is totally irrelevant.

    I strongly recommend you remove all lookups and formatting in tables. Users will not (should not) be looking at tables directly so what they look like is irrelevant. In the meantime it has confused you and created hours of additional and unnecessary work.

    You are also appear to be using multivalue fields incorrectly. Again remove them and do it properly. They are a gimmick which will trip you up in the future because you are passing some control of what you can do to access - and at some point you will find you wont be able to do what you want to do beacause of the multivalue field.

    Beaten to the punch on my last two paras

  8. #23
    raychow22 is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    May 2017
    Posts
    145
    Thank guys! for the suggestions.

    Ill try my best to do as you suggested. I've only been playing with Access for a month now. I was importing a lot of excel fields and messing with it a lot so I know my db has a lot of flaws to it.

    Ajax, it's weird that my formNCR is called ...... tblNCR1 <--- I don't know why this is....

    But going back to the original thread question. formNCR is controlsourced in tblNCR. I want Ntype: NCR or CDR to Dmax the value in tblNCR where "NCR" or "CDR" increments their own set of numbering. Was there anything wrong with the code?

    Thanks!

  9. #24
    ssanfu is offline Master of Nothing
    Windows XP Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    Quote Originally Posted by raychow22 View Post
    Can you guys take a look. Also take a look at my other tables and forms and suggest any improvements on them. Thanks!!
    OK, My $0.02...


    Apparently in this version of the dB you have split the records for NCR and CAR into separate tables (and maybe "DIR" records).
    So the code
    Code:
            Me.NCRNumber = Nz(DMax("[NCRNumber]", "tblNCR"), 0) + 1
    appears to be working.




    - EVERY code module (form,report,standard) should have these two lines as the first two lines:
    Option Compare Database
    Option Explicit


    - In 5 places in the code, you have the command "DoCmd.Save"
    This does not save the data. It is to save changes in the object (ie form/report/...) design.

    - The report has a prefix that indicates it is a form...??
    Since it is a report, I would not have any buttons/code behind the report except the "OnNoData" event or Min/Max report events.
    There is code for the "Form_Open" event & "Form_BeforeInsert" event.... but they shouldn't be there for a report.



    I would suggest stepping back and go back to the drawing board. Design the dB using a whiteboard, cardboard, paper, stickies,....
    Test the design BEFORE opening Access.


    Good luck with your project.....

  10. #25
    raychow22 is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    May 2017
    Posts
    145
    The code works without the criteria bit to it. I just don't understand why the criteria cant work...

  11. #26
    ssanfu is offline Master of Nothing
    Windows XP Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    Quote Originally Posted by raychow22 View Post
    But going back to the original thread question. formNCR is controlsourced in tblNCR. I want Ntype: NCR or CDR to Dmax the value in tblNCR where "NCR" or "CDR" increments their own set of numbering. Was there anything wrong with the code?
    I do not see a combo box named "NType" on form "formNCR". You do have a form event "BeforeInsert" that adds the NCR number.

    As for the "tblNCR1", open "formNCR" in design view, open the properties sheet, and click on the FORMAT tab. The first property, "Caption" is where you can set a name for the form that is displayed at the top left of the form.

  12. #27
    ssanfu is offline Master of Nothing
    Windows XP Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    It CAN work, but there is not a table "tblNCRCDR" in the dB you posted. Similarly, the form image you posted in Post #6 is not in the dB. "fromNCR" is different that the image...

  13. #28
    raychow22 is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    May 2017
    Posts
    145
    ssanfu,

    The tblNCRCDR is just a table for NCR or CDR in which I rowsource in the form combo box Ntype. I want the criteria to look at tblNCR under field "Ntype" for NCR/CDR and take the max number from "NCRNumber" to the next increment number.

  14. #29
    ssanfu is offline Master of Nothing
    Windows XP Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664

    The tblNCRCDR is just a table
    That is just it.
    I do not see a table "tblNCRCDR"
    I do not see field "Ntype" in table "tblNCR".
    I do not see a combo box "Ntype" on any form.

  15. #30
    raychow22 is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    May 2017
    Posts
    145
    Quote Originally Posted by ssanfu View Post


    That is just it.
    I do not see a table "tblNCRCDR"
    I do not see field "Ntype" in table "tblNCR".
    I do not see a combo box "Ntype" on any form.
    QC Checklist1.zip

    How about now?

Page 2 of 4 FirstFirst 1234 LastLast
Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 12
    Last Post: 12-25-2015, 12:47 AM
  2. DMAx Question for Report - DMax <= Dtae
    By crimedog in forum Access
    Replies: 8
    Last Post: 12-29-2014, 09:31 PM
  3. Replies: 4
    Last Post: 05-08-2014, 11:46 AM
  4. DMax with LIKE in criteria
    By tylerg11 in forum Access
    Replies: 5
    Last Post: 07-26-2013, 10:39 AM
  5. Replies: 6
    Last Post: 07-24-2012, 03:02 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums