Page 2 of 2 FirstFirst 12
Results 16 to 28 of 28
  1. #16
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,536
    Thanks
    0
    Thanked 23 Times in 23 Posts
    I did something like this many years ago, I would generate a UNION query on the fly to access all the databases (84 in all, each with 2 million records).
    So I would tend to use a UNION query as a basis to search for the records, then use queries that use the Union query.
    I think I also linked tables on the fly too.
    Last edited by patt; 2014-08-13 at 23:40.

  2. #17
    New Lounger
    Join Date
    Jul 2008
    Posts
    19
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Thanks again.

    I have built a basic search form using a Query and Button to run it for FE table. I am still having 2 issues as below

    1. I am still facing issues while importing the data from excel to FE table which then to 3 different BE tables. My Excel has 24 Fields, while DB has 27 Fields, Those 3 fields are Date & Time of Import, Username of Import & Condition status. Either I see the DB Table Structure gets overridden or no import is happening. Pls suggest.

    2. I have created a Union Query by consolidating all the Tables (both FE & BE). How should I declare the Parameter, so that I can use it for the Search form which I created ?

  3. #18
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,536
    Thanks
    0
    Thanked 23 Times in 23 Posts
    1. have you tried importing the excel spreadsheet to a non-existant table? It should bring all the fields in. If that works, why not delete the table before importing the spreadsheet.

    2. Use another query that uses the union query as it's source.

    Are you using ODBC to reference those BE tables?

  4. #19
    New Lounger
    Join Date
    Jul 2008
    Posts
    19
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Thanks Patt. Not tried it yet...

    No I am not using the ODBC reference... Its just the normal Queries & forms accessing the both the FE & BE.

    Need ideas on below;
    1. Any suggestions on capturing the Date & Time for each Import along with Username for each Import?
    2. If I convert the DB to an EXE file to protect the DB from an editable form, Can the Queries, Forms, Reports, Macros & Other Access objects be added to the DB in EXE file?

  5. #20
    New Lounger
    Join Date
    Jul 2008
    Posts
    19
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Need ideas on below;
    1. I tried capturing the Username for each Import, So I tried calling the User-defined Function through table design, but User-defined functions do not appear. Pls suggest....
    2. Any suggestions on capturing the Date & Time of Import ?
    3. If I convert the DB to an EXE file to protect it from editing, Can I add new Queries, Forms, Reports, Macros & Other Access objects to the DB EXE file in the future?

  6. #21
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,536
    Thanks
    0
    Thanked 23 Times in 23 Posts
    Need ideas on below;
    1. Any suggestions on capturing the Date & Time for each Import along with Username for each Import?
    Where do you wish to capture the Date & Time for each Import along with Username for each Import?

    2. Any suggestions on capturing the Date & Time of Import ?
    Answer q1 first.

    3. If I convert the DB to an EXE file to protect it from editing, Can I add new Queries, Forms, Reports, Macros & Other Access objects to the DB EXE file in the future?
    An EXE cannot be created, however an MDE can be created, tables and queries are editable as far as I know.

  7. #22
    New Lounger
    Join Date
    Jul 2008
    Posts
    19
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Hi,

    Point 1 & 2. I want the Usernames to be an Auto calculated field in a Table where Iimport. We import 28 fields from excel, while Access has 30 Fields. 2 excess fields are Username of the Person who imports & Date of Import..Help required here..

    Point 3: If I Convert Access Database (ACCDB) to EXE File to prevent users from editiing the DB, I wanted to knw whether Any Future Access objects can be addded to the DB in EXE mode created already or Should I need to create a new DB in exe mode with those enhancements ?

  8. #23
    New Lounger
    Join Date
    Jul 2008
    Posts
    19
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Thanks to all. I have managed to Capture Username and Time of Import through Update SQL.

    Now I require help on the below:

    1. I am designing a Search form from Union SQL of all 4 tables (3 Backend Tables). I want only important 10 fields to display instead of all 27 fields.
    2. I want to Add & Edit records through this Search Form to the Table I wish.
    3. I also want a Check box to be displayed on the side, where I will select few records to change the fields I want, where this checkbox is mere selection and does not represent any fields in all the tables.

  9. #24
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,496
    Thanks
    3
    Thanked 42 Times in 42 Posts
    Thanks to all. I have managed to Capture Username and Time of Import through Update SQL. (Note that this could also be done by setting defaults for the 2 added fields.)

    Now I require help on the below:

    1. I am designing a Search form from Union SQL of all 4 tables (3 Backend Tables). I want only important 10 fields to display instead of all 27 fields. Using a bound form, you can choose to display any or all of the fields in a record - do you intend to display only one record at a time?
    2. I want to Add & Edit records through this Search Form to the Table I wish. In general Union queries are not editable since the result doesn't show what table the record came from. In order to edit a record in the correct table you will need to do a good deal of VBA programming, using either DAO or ADO to find the specific record in the specific table and make the desired changes.
    3. I also want a Check box to be displayed on the side, where I will select few records to change the fields I want, where this checkbox is mere selection and does not represent any fields in all the tables. If you wish to review records and set the check mark and then come back and edit just those records, you will need to add a check mark field to each of the tables involved, and you will need to be able to apply a filter to the form to find the records that need editing.

    Based you your last two questions, and the complexities associated with implementing such a solution, I would strongly suggest you consider hiring a consultant to assist you with your project.
    Wendell

  10. #25
    New Lounger
    Join Date
    Jul 2008
    Posts
    19
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Hi WendellB ,

    I m still trying this on my own. I m not able to understand Allen Browne's sample. It goes over my head
    Attached is the form I am working on... my requirement is to search using either or all parameters in form, based on the results I will change the status in dropdown and save the records. I tried using his query which throws me no record nor the records selectors are enabled to select, modify and save.... Please help.

    Private Sub cmdSearch1_Click()
    Dim strWhere As String 'The criteria string.
    Dim lngLen As Long 'Length of the criteria string to append to.
    Const ChqDate = "\#dd\/mm\/yyyy\#" 'The format expected for dates in a JET query string.

    'Contract No
    If Not IsNull(Me.TxtContractNo) Then
    strWhere = strWhere & "([Contract Number] = "" & Me.TxtContractNo & "") AND "
    End If

    'Cheque No
    If Not IsNull(Me.TxtChequeNo) Then
    strWhere = strWhere & "([Cheque No] Like ""*" & Me.TxtChequeNo & "*"") AND "
    End If
    'From Date fields
    If Not IsNull(Me.TxtFromDate) Then
    strWhere = strWhere & "([Cheque Date] >= " & Format(Me.TxtFromDate, ChqDate) & ") AND "
    End If

    'To date
    If Not IsNull(Me.TxtToDate) Then 'Less than the next day.
    strWhere = strWhere & "([Cheque Date] < " & Format(Me.TxtToDate, ChqDate) & ") AND "
    End If
    'See if the string has more than 5 characters (a trailng " AND ") to remove.
    lngLen = Len(strWhere) - 5
    If lngLen <= 0 Then 'Nah: there was nothing in the string.
    MsgBox "No criteria", vbInformation, "Nothing to do."
    Else 'Yep: there is something there, so remove the " AND " at the end.
    strWhere = Left$(strWhere, lngLen)

    'Applying form's Filter.
    Me.Filter = strWhere
    Me.FilterOn = True
    End If
    End Sub
    Attached Images Attached Images

  11. #26
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,536
    Thanks
    0
    Thanked 23 Times in 23 Posts
    Is ContractNo text? If not remove the surrounding single quotes.
    Is Cheque No text? if not remove the surrounding single quotes.
    The format for the dates is "mm/dd/yyyy".

    Take the query of the form and create a new query, and in the SQL of the query plant the contents of strWhere and fiddle until you get it right.

    When you get the strWhere clause right and it still doesn't work, try reversing the order of:
    Me.Filter = strWhere
    Me.FilterOn = True
    Last edited by patt; 2014-09-20 at 19:35.

  12. #27
    New Lounger
    Join Date
    Jul 2008
    Posts
    19
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Hi Patt. It didn't work still. Please could you help/advise me on my form ?

  13. #28
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,536
    Thanks
    0
    Thanked 23 Times in 23 Posts
    You can't just say no it didn't work, what didn't work, please be more specific else we have no idea how to help.
    Did you do what I suggested about creating a new query?
    Are those fields text fields?
    It would be a lot easier if you posted a compacted, zipped database and you telling us what values you are trying for the search fields.

Page 2 of 2 FirstFirst 12

Tags for this Thread

Posting Permissions

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