Results 1 to 4 of 4
  1. #1
    UT227 is offline Expert
    Windows 7 32bit Access 2013 32bit
    Join Date
    Feb 2016
    Posts
    581

    Search fields

    I have a form [frmCrimePreventionStats]. It gets its information from a query [qryCrimePreventionStats3]. That query gets information from the table [tblCrimePreventionStats]. What I want that form to do is to be called up to the current month and year. If there is no information, I want it to add a new record. If information has been added, I want that information to be displayed. Also, I want to be able to search by changing month and year on that form. I have the query set up with the month and year getting the information from the form. The month field [cboMonthID] and year field [ReportYear] requeries on the after update. What I have done isn’t working.


    Click image for larger version. 

Name:	error.jpg 
Views:	36 
Size:	117.0 KB 
ID:	36146
    When I change my month, it gives me an error saying that it will duplicate a record because I have it indexed so that you can’t enter the same month and year on 2 records. So, it isn’t searching. How can I set it up so that the form will enter information on a new record and also search existing records by changing the month and year?

  2. #2
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    There could be multiple issues. First, the name of the form in your post text and query design picture aren't the same. Second, you don't bind search combos to fields. Doing so will cause Access to attempt to change the data in the bound field according to the search value chosen. Reads like you have done this.

    Some of your post doesn't make complete sense. Forms don't enter records all by themselves - users use forms to enter/edit records, so I don't grasp the goal when a form doesn't show a record for a month. If a user selects month and year criteria and you code to validate these unbound controls, the form will either show records or not (BTW I don't see the point in combos for this unless you're applying some sort of restriction - why not just a textbox(es), perhaps even making use of the built in calendar). If no records result, you could provide a prompt to enter a new record, in which case a Yes response causes the form to move to a new record. Not sure what you have in mind at this point, because as I said, forms don't append records without user intervention. A query could if there is a trigger event, but still, there has to be some sort of intervention.

    You might want to search on the basics of making/using search forms and see if that fits with your process. I say that because a) of your apparent use of bound search criteria controls and b) maybe you need to rethink the process. Perhaps a separate search form and then open the 2nd form in accordance with the search result?
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  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
    Here is tutorial on search form http://allenbrowne.com/ser-62.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.

  4. #4
    UT227 is offline Expert
    Windows 7 32bit Access 2013 32bit
    Join Date
    Feb 2016
    Posts
    581
    Thanks. I'll look it over. I forgot that I was using bound fields.

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

Similar Threads

  1. Replies: 3
    Last Post: 08-21-2018, 03:11 PM
  2. Replies: 6
    Last Post: 08-22-2013, 08:47 PM
  3. Search fields SQL
    By noobaccess in forum Access
    Replies: 20
    Last Post: 12-09-2012, 07:18 PM
  4. Help with Search Fields
    By jhawk in forum Forms
    Replies: 2
    Last Post: 03-16-2011, 08:42 AM
  5. Help with Search Fields
    By jhawk in forum Programming
    Replies: 0
    Last Post: 03-15-2011, 08:05 AM

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