Page 2 of 2 FirstFirst 12
Results 16 to 20 of 20
  1. #16
    Micron is offline Virtually Inert Person
    Windows 7 32bit Access 2007
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    Re: the secrecy - understood and not an issue. Your current explanation might be able to provide a more focused direction. Pseudo data that's way off the mark usually results in suggestions that are not practical and questions that are not pertinent. You could do as you are proposing as in
    tblCN
    ID CNsn ItemSNa ItemSNb
    1 00111 S123 S0123
    2 00222 S345 S0234
    3 00333 S456 S0345
    4 00444 S567 S0456
    5 00555 S678 S0567
    6 00666 S789 S0678
    and if the 2 serial fields are composite, then the table would handle the data as mentioned. Presumably, the carton sn field would have to be a unique index on its own as well. However, this isn't the normalized approach. The correct approach would be a linking table that represents the combination of cartons and sn's. Presumably there is a table of phone sn's but rather than mock that as well, I just put those sn's directly in the linking table. In actual practice, the phone table sn id's would be used so as to not repeat the sn's.


    This is the carton sn table:
    tblCN
    ID CNsn
    1 00111
    2 00222
    3 00333
    4 00444
    5 00555
    6 00666

    this is the linking table. CNsn_FK: fk in a fld name is oft used to indicate that the field is a field in some other table (usually that table's PK field).
    tblCNSN
    ID CNsn_FK ItemSN
    1 1 S123
    2 1 S0123
    3 2 S345
    4 2 S0234
    5 3 S456
    6 3 S0345
    7 4 S567
    8 4 S0456
    9 5 S678
    10 5 S0567
    In this case, a unique index on carton field isn't required, but the composite on CNsn_FK and ItemSN would mean that the same sn can't go into the same carton 2x. This doesn't ensure that S123 can't go into carton 1 and 2. I don't think anything proposed thus far addresses that possibility as it would require more robust checking. As for what event, such validation has to occur before updating. AfterUpdate is too late.

    The main issue with idea #1 is that should a 3rd item end up going into the carton, you have to modify the table and likely every form, report and query that uses it. While this exact issue may never arise, the concept of side by side sn's fails the acid test and should be avoided in most cases.

  2. #17
    MtDew4243 is offline Novice
    Windows 10 Access 2016
    Join Date
    Feb 2018
    Posts
    13

    Form for data entry

    Micron,

    Thank You! By the way, how was your drive?

    I agree the way I was taught was not to have two fields comprising of the same item. (Forgetting my jargon, it doesn't meet normalization - first, second, or any) I like your cleaner approach of a table for Carton SN, Item SN, and a linking table of the two: tblCNSN. The gap in my knowledge is how do I then create a Form to collect that data. Any form I think to make using your table structure has only three fields with one auto populated.

    frmDataEntry: This form would work if I wanted to enter the CNsn twice. Once with the first ItemSN and once with the second ItemSN. (To create two records.)

    AutoID CNsn ItemSN
    1 00111 S123




    How do I make a form that allows for the correct data to be related by only entering the CNsn once? The form has to have 4 fields doesn't it? (But then isn't it only a single record update?)

    frmDataEntry2

    AutoID CNsn ItemSN ItemSN
    1 00111 S123 S456

    Sorry I'm so dense in this... I can't wrap my head around how to collect the data using a form if I only have a single field for ItemSN.

    Also per my dialog with June7, I might as well ask at this time as well. Once I create my form, I would prefer each field to be verified before preceding to the next.

    ie Check CNsn before tabbing to ItemSN. Checking ItemSN before updating record.

    Then if there was an error, highlight (select) all the incorrect data so once the correct data was being re-entered the bad data would be automatically replaced. I'm trying too minimize keystrokes by not having to press the backspace key for however many characters comprise the data.

  3. #18
    Micron is offline Virtually Inert Person
    Windows 7 32bit Access 2007
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    Wasn't the nicest drive I've ever had, but not the worst. I managed to stay out of the ditch unlike several others.
    I advocate queries before forms. If the query doesn't work (allow appends, updates or deletes) it's not much good for this. Then comes the form. Obviously the tables and some test data comes first. IMHO it's the only valid reason for entering data directly into tables.

    There's always more than one way. Here's one:
    Basically, the form type you'd use would be a form/subform setup. The main form would have an unbound combo if you want to select carton numbers (if you have them all entered) but for entering carton numbers, a textbox might be easier for you than dealing with adding data through a combo. Not sure I see the use of a combo unless you want to be able to select existing carton data for editing.

    This form would have a subform on it where based on the carton sn showing in the main form, you add phone sn's in a row (datasheet or continuous form) fashion in the subform. IF you create relationships on this premise, you will not be able to add records in the subform without a saved carton sn existing. You would need a code check before each subform append starts to ensure a) a 3rd record isn't being attempted (BeforeInsert) which means getting the subform record count and cancel if it's already 2; and b) to ensure there is a saved carton sn in the main form control. That's the general idea. If you have not done so, read up on main form sub form design as it's a bit extensive to tell you much more than that in this forum.

    There is also a split form, which I confess to having rarely (if ever) used beyond just playing around. You could check out the feasibility of that as well. As always, if my peers have a better idea on how you should approach this, I hope they chime in for you.

    As for the highlighting (I think you mean selecting the contents of a control) it's one of the easier parts of your task. You would want to get the thing functioning correctly first, then add bells and whistles. If you can think of it, it can probably be done. However, I think the whole task requires more than a rudimentary understanding of Access. I wouldn't expect to wrap this up in a day or two if you're a novice.

  4. #19
    MtDew4243 is offline Novice
    Windows 10 Access 2016
    Join Date
    Feb 2018
    Posts
    13
    Thank You Everyone!! I was able to complete this project!

  5. #20
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,822
    You can either set the field to Null or select its contents:

    Me.textboxname.SelLength = Len(Nz(Me.textboxname, ""))
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

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

Similar Threads

  1. Replies: 6
    Last Post: 09-02-2016, 09:55 AM
  2. Data Capture app
    By Homegrownandy in forum Access
    Replies: 8
    Last Post: 09-24-2015, 09:02 AM
  3. Access to Word mail merge - data format
    By Lex_iuk in forum Import/Export Data
    Replies: 6
    Last Post: 06-24-2015, 06:49 AM
  4. Replies: 3
    Last Post: 05-08-2015, 10:05 AM
  5. MS Word using merge with Access-data
    By Eef in forum Reports
    Replies: 6
    Last Post: 10-20-2014, 03:14 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