Results 1 to 10 of 10
  1. #1
    scott0_1 is offline Advanced Beginner
    Windows Vista Access 2013 32bit
    Join Date
    Apr 2018
    Posts
    82

    Continuous form is showing first record 680 times???


    Hi all,

    I've got a continuous form where I'm setting the record source on form load. This has been causing me some issues all day. I have a SELECT CASE based on what the user has chosen. The first case, written the same way, works fine. The second case, below, shows the right number of records, but only displays the data from the first record.

    Any ideas???

    Code:
    strSQL = "SELECT tblExcelImportPositionNumbersTEMP.Field1, tblExcelImportPositionNumbersTEMP.Field2 FROM tblExcelImportPositionNumbersTEMP;"
    Me.RecordSource = strSQL
    txtField1 = Me!Field1
    txtField2 = Me!Field2

  2. #2
    scott0_1 is offline Advanced Beginner
    Windows Vista Access 2013 32bit
    Join Date
    Apr 2018
    Posts
    82
    So I kind of figured it out, but I would like someone to clarify for me when I can set the control source for a textbox. On the property sheet, I just typed in the name of the control source. That is what solved the problem. But when can I assign the control source in VBA? I tried to do it in the Form Load event earlier, but then I got #Name? in the textbox. Do I need to set that property on Form Open before defining the record source?

    Code:
    Form_Open
    textbox1.controlsource="Field1"
    
    Form_Load
    Me.Recordsource = "SELECT tblExcelImportPositionNumbersTEMP.Field1, tblExcelImportPositionNumbersTEMP.Field2 FROM tblExcelImportPositionNumbersTEMP;"
    ???

    I really want to understand as this seems pretty fundamental to me.

    Thanks,

    Scott

  3. #3
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,816
    Why are you dynamically setting RcordSource and ControlSource? I have done this in form Open event. However, I prefer to avoid programmatically setting RecordSource.
    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.

  4. #4
    scott0_1 is offline Advanced Beginner
    Windows Vista Access 2013 32bit
    Join Date
    Apr 2018
    Posts
    82
    I'm using one form to show different data from different tables. Since they layout of the form is the same for each display, it would be a waste to make four different forms. It should be easier to use the same form and change the record source.

  5. #5
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,816
    Now I have to ask why you have 4 tables with same data structure and not 1 table with another field for category and filter records by that category?
    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.

  6. #6
    Join Date
    Apr 2017
    Posts
    1,673
    Quote Originally Posted by June7 View Post
    Now I have to ask why you have 4 tables with same data structure and not 1 table with another field for category and filter records by that category?
    I had at 1st the same question, but then remembered table name "tblExcelImportPositionNumbersTEMP". It looks like OP has 4 different linked Excel tables with same structure, from where he/she wants read data.

    Anyway, I think when this is the case, the next best option is to have an Open event of main form to read all data from linked tables to Access table with additional field for source table identification, and after that to continue with continuous form based on access table and filter.

    The Access table must have a composite primary key, where one field is source table identifier.

    The Open event must:
    delete all old records from Access table;
    Run an UNION append query to add all records for all linked tables + adding source table identifiers, or run an append query for every source table;

    In case there may be need to reread linked info through session anew, there must ba a button on some form, which runs same code as Open event.

    In case you (OP) want to use current design anyway, then you have to rewrite form's RecordSource property depending on some selection. I.e. you must have e.g. an unbound combo somewhere on form, where you select the source table. This combo must have an AfterUpdate event, which rewrites the RecordSource property of form, and refereshes the form afterwards. NB! The name of source table in RecordSource must be written into sql-string, not referenced. In case source tables have different field names, you have to rename them in query, so the query always returns the same set of names. Form's linked controls must be have query fields as ControlSource, and those are not edited at all.

  7. #7
    scott0_1 is offline Advanced Beginner
    Windows Vista Access 2013 32bit
    Join Date
    Apr 2018
    Posts
    82
    I may be making this really difficult for myself, but here is what is happening (real life workflow):


    1. User gets a large amount of data from another computer system in Excel format. It is not at all usable by the average user here.
    2. The user copies and pastes one, two or three columns of data (based on what it is they're trying to import) from that export into a new Excel sheet. I chose to avoid linked Excel sheets because:
      1. Too many users that don't have access to the same network drives and
      2. One of the users would ruin the sheet or file somehow. No matter how much protection I try to put on the sheet. Guaranteed.

    3. In the database, there is a form where the user will press a single button which will import that new sheet into the tblExcelImportPositionNumbers.Field1 and/or tblExcelImportPositionNumbers.Field2 and/or tblExcelImportPositionNumbers.Field3. The table name was created before I chose to make it a generic table. Originally I was going to make tables for each different process, but then I realized I could use the same temporary table to import all the data, no matter what it was.
    4. A form pops up in the database showing the user the data in the temporary table so they can compare it to the data in the Excel sheet. It then asks for them to confirm or cancel.
    5. In cmdConfirm_Click, it puts the data into the appropriate tables based on what data the user originally told the database it was.


    This process I'm asking about is to populate the "Confirm" form. I've managed to get it done like I said, but their has to be a clean way to set the record source and control source through VBA. The only way I got it to work was to use generic names like field1 and field2 and bind those in the property sheet.

    If I had gone my original way with different tables and different names for the field, that would have been a cluster. I'm glad I realized that I could do it this way. The only problem now is when I'm working with the temporary tables, I have to remember what data is in field1, field2 and field3 based on the users' selection. It just means a little more brain power, which I don't have of much to waste.

    Scott

  8. #8
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,816
    I am confused by last paragraph. "If I had gone my original way with different tables ...". How is "original way' different from what you implemented? Still don't understand why not 1 table.
    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.

  9. #9
    scott0_1 is offline Advanced Beginner
    Windows Vista Access 2013 32bit
    Join Date
    Apr 2018
    Posts
    82
    It is 1 table now. The original thought was to create multiple tables for the temporary import.

    I think we're focusing on the wrong idea here though. The big question was dynamically setting the record source and control source in VBA.

    I really appreciate all the help. I don't mean that last sentence to sound snarky... I just really want to know how to do it!

    My education in this stuff is dated. I took a three year Computer Information System program in college and graduated in 2002. I haven't used any of this since! Most of the concepts are still up there, but talk about cobwebs! Some one in my company found out that I have a background in this and they've asked me to make a database that is maybe a little out of my league!

  10. #10
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,816
    I stated in post 3 I have done this in form Open event.

    Have also set RecordSource in non-event proc that is called by some event such as button click.
    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.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Go to certain record on continuous form
    By LonghronJ in forum Modules
    Replies: 4
    Last Post: 05-07-2018, 12:08 PM
  2. Replies: 8
    Last Post: 02-26-2018, 05:21 AM
  3. Moving to record on a continuous sub-form
    By tonez90 in forum Access
    Replies: 4
    Last Post: 09-11-2017, 04:46 PM
  4. Continuous form showing new record
    By NISMOJim in forum Forms
    Replies: 4
    Last Post: 07-25-2015, 01:03 PM
  5. Replies: 3
    Last Post: 06-27-2009, 03:53 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