Page 2 of 5 FirstFirst 12345 LastLast
Results 16 to 30 of 74
  1. #16
    Mohanss82 is offline Advanced Beginner
    Windows 8 Access 2010 32bit
    Join Date
    Apr 2014
    Location
    India
    Posts
    44
    Sorry, I couldn't understand your previous suggestion.

    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?

  2. #17
    June7's Avatar
    June7 is online now VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,822
    Exactly what do you not understand about the suggestion in post 15?

    1. Cannot call UDF from table, can call from query.

    2. Again, my suggestion for capturing date and time of import is to use an UPDATE sql to populate this value into newly imported records.

    3. Already answered - cannot modify exe. Would have to modify the original accdb and republish the exe.
    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.

  3. #18
    Mohanss82 is offline Advanced Beginner
    Windows 8 Access 2010 32bit
    Join Date
    Apr 2014
    Location
    India
    Posts
    44
    Many Thanks for the prompt revert...

    1. If UDF can't be called, how should I capture the Username ?
    2. Can you help me or suggest me some links for UPDATE Sql ?
    3. Will this FE Updater work ?
    http://autofeupdater.com/index.htm

  4. #19
    June7's Avatar
    June7 is online now VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,822
    1. Maybe set DefaultValue property of textbox to call the UDF.

    2. Good site for learning SQL http://www.w3schools.com/SQl/sql_update.asp
    Use Access query builder to help build the UPDATE sql.

    3. Heard of it, never used. I have code in my db to accomplish. Review https://www.accessforums.net/code-re...end-33331.html
    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.

  5. #20
    Mohanss82 is offline Advanced Beginner
    Windows 8 Access 2010 32bit
    Join Date
    Apr 2014
    Location
    India
    Posts
    44
    Thanks for the suggestion. 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). important 10 fields to display in Datasheet view 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 All/Some records to change the fields I want, where this checkbox is mere selection and does not represent any fields in all the tables.

  6. #21
    June7's Avatar
    June7 is online now VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,822
    Cannot add/edit through UNION query. UNION is not an editable query.

    Why do you need UNION? Why do you have 4 tables? Are they identical in structure? Should be 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.

  7. #22
    Mohanss82 is offline Advanced Beginner
    Windows 8 Access 2010 32bit
    Join Date
    Apr 2014
    Location
    India
    Posts
    44
    Due to Access Table size limitation of 2GB, I have split the data to 4 tables. All the tables structure is same. Pls suggest a way around ?

  8. #23
    June7's Avatar
    June7 is online now VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,822
    Never had to do that. Is the break based on year?

    Options:

    1. code that changes the RecordSource of a single form based on which table the record is in

    2. tab control with 4 tabs, each table is a subform on a tab
    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. #24
    Mohanss82 is offline Advanced Beginner
    Windows 8 Access 2010 32bit
    Join Date
    Apr 2014
    Location
    India
    Posts
    44
    Thanks June. I have changed the BE tables from 3 to 1.

    I have created a new Field with checkbox to select, which will be used to select to edit records. Now I have created Search form to search & edit the data from that 1 BE Table by using the below VB command.
    DoCmd.ApplyFilter , "[TxtContractNo]= "" & Me.cmdSearch & "" OR [TxtChequeNo]= "" & Me.cmdSearch & "" & "Cheque Date >=[TxtFromDate] And <=[TxtToDate]".

    but I am getting an Compile error: Syntax Error. Please help.....

  10. #25
    June7's Avatar
    June7 is online now VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,822
    I've never used ApplyFilter. I use FilterOn property. Review http://allenbrowne.com/ser-62code.html

    Are these field names or textbox names?

    TxtContractNo
    TxtFromDate
    TxtToDate

    Use apostrophe delimiter or need to double up the quote marks. I use apostrophe. Be careful with parens when mixing AND and OR operators. Need AND operator before [Cheque Date]. [Cheque Date] has a space so need []. Need # delimiters for date parameters. Try:

    "([ContractNo]='" & Me.cmdSearch & "' OR [ChequeNo]='" & Me.cmdSearch & "') AND [Cheque Date] BETWEEN #" &[TxtFromDate] & "# AND #" & [TxtToDate] & "#".
    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.

  11. #26
    Mohanss82 is offline Advanced Beginner
    Windows 8 Access 2010 32bit
    Join Date
    Apr 2014
    Location
    India
    Posts
    44
    The ones mentioned above starting with Txt are Text box names.
    Fields Names are ContractNo, ChequeNo, Cheque Date. Below Is the code I tried now with compile error. Pls help.

    Private Sub cmdSearch_Click()
    Me.FilterOn , "([ContractNo]='" & Me.cmdSearch & "' OR [ChequeNo]='" & Me.cmdSearch & "') AND [Cheque Date] BETWEEN #" & [TxtFromDate] & "# AND #" & [TxtToDate] & "#"
    End Sub

  12. #27
    June7's Avatar
    June7 is online now VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,822
    Look at the Allen Browne article again. The procedure conditionally builds filter criteria. If user does not provide input for any parameter then the criteria string does not include it.

    Me.Filter = strWHERE
    Me.FilterOn = True

    Controls used to input filter criteria must be UNBOUND.

    Just hit me that cmdSearch must be a command button. Why would you refer to a command button for filter parameter?
    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.

  13. #28
    Mohanss82 is offline Advanced Beginner
    Windows 8 Access 2010 32bit
    Join Date
    Apr 2014
    Location
    India
    Posts
    44
    Hi June,

    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.

    Code:
    Private Sub cmdSearch1_Click()
    Dim strWhere As String
    Dim lngLen As Long
    Const ChqDate = "\#dd\/mm\/yyyy\#"

    '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 Thumbnails Attached Thumbnails Capture.PNG  

  14. #29
    June7's Avatar
    June7 is online now VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,822
    What do you mean you 'tried using his query'? What his query? Do you mean the code to build filter criteria?

    I don't see anything wrong with your code. But not seeing any data in the record. Is the form bound to table/query?
    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.

  15. #30
    Mohanss82 is offline Advanced Beginner
    Windows 8 Access 2010 32bit
    Join Date
    Apr 2014
    Location
    India
    Posts
    44
    Sorry June,

    I meant his code to filter records but mentioned as his Query.

    I am not getting any results, if I make the parameter as exact values, record selectors are not enabled. I have enabled them in the form properties..

    This is bound to the BE Table.

    Require some urgent help on this ?

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

Similar Threads

  1. Can not import any excel 2010 spreadsheet into access 2010
    By BobsWright in forum Import/Export Data
    Replies: 4
    Last Post: 09-26-2013, 12:44 PM
  2. Replies: 3
    Last Post: 08-02-2013, 03:46 PM
  3. Replies: 2
    Last Post: 12-26-2012, 02:58 PM
  4. Replies: 4
    Last Post: 09-19-2012, 02:07 AM
  5. Exporting 2010 Web Database report to Excel
    By rogstepper in forum Import/Export Data
    Replies: 2
    Last Post: 09-12-2012, 01:52 PM

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
  •  
Other Forums: Microsoft Office Forums