Data Entry form look help

[TroxlSha]

I am building a database for the least technologically savvy individuals (it seems I may be in that category). The form is set to allow data entry only, as without this the user would over-right previous data. The concern now is they are not inputting all of the data at one time. So I am having 2 and 3 rows for one specific time frames data. I am looking for a way so that when they input the date and time it would search for a record matching those criteria and either go to that record or begin a new one. Is this possible?

[June7]

Yes, is possible. I would use VBA code (I don't use macros). The how depends on what you want.

Could have a form that has unbound controls for input of those criteria then code will open the data entry form to the record meeting criteria, if record exists, if not then open to a new record.

Or can have the unbound controls on the same form that displays the data. Here is one way http://datapigtechnologies.com/flash...tomfilter.html

[Missinglinq]

Some questions:

1. Is Date/Time being entered into one Field or two separate Fields?
2. Is/are the Field or Fields defined as Date/Time Datatypes or something else?
3. How exact is the Time portion, i.e. are we talking about 6:15, 8:45 or are we talking about 7:07, 9:27?
4. How, exactly, are you setting this Form to 'allow data entry only?' And by this, do you mean to only be able to enter New Records?
5. What kind of Form are we talking about: Single, Continuous or Datasheet View?
6. Are we talking about about several possible separate 'time frames' (whatever that is) for a given date, or possibly many, many time frames per given date?

I can think of a number of possible approaches, including June7's suggestion of having an Unbound Form with Controls for entering the Date and Time portions of a time frame, but they depend on the answers to the above questions.

Linq ;0)>

[TroxlSha]

Thank you for the quick replies. I will have a look at the link you have provided June7

Missinglinq, as for your questions...
1. Yes the Date and Time fields are two separate fields
2. They are defined as date/time datatypes
3. The "time frames" are actually static. It is for specific test which must be conducted at a certain time each day. So it is always 0000, 0400, 0800, 1200, etc...
4. I have the Data Entry: Yes selected in the form properties making it show a new record each time it is opened. After they finish entering the I have a button to exit the form and return to the switchboard.
5. It is a Single view form
6. There are a total of 6 time periods possible.

[TroxlSha]

I was talking with a friend of mine that did some programming a while back, but mainly dealing with Pearl. This database has a main switchboard with command buttons to open up various forms. Each form is linked to a different table for data entry. All of these have a Date and Time field (separate field). Would it be easier if I were to have the user input the date/time they are wishing to create and/or modify on the switch board leaving the option to create event on click to query the appropriate database for those values, and if found have it go to that record, if not have it enter as data entry mode.

[June7]

Not really. The issue here is using date and time as search criteria. Never seen this. What is nature of this database?

[Missinglinq]

...The form is set to allow data entry only, as without this the user would over-right previous data...

...I am looking for a way so that when they input the date and time it would search for a record matching those criteria and either go to that record or begin a new one...

When the Data Entry Property is set to 'Yes' you can only enter New Records; you cannot view Existing Records, and hence you cannot search Existing Records, which you want to do. To take care of the problem in red, above, I would go back and set the Data Entry Property to 'No.' Then I would add this code to the Form:

Private Sub Form_Load()
  DoCmd.GoToRecord , , acNewRec
End Sub

When the Form is opened, it will immediately go to a New Record, rather than going to the first Record being displayed, but you will now be able to view and search the existing Records.

As to the problem in blue, above, I would place two Textboxes in the Form Header to enter the date and time to search for; name them SearchDate and SearchTime.

Next, place a Command Button in the Header; name it cmdSearchOrCreateNew. Now place this code behind the Command Button:

Private Sub cmdSearchOrCreateNew_Click()

 Dim rs As Object

 Set rs = Me.Recordset.Clone
    
   If Nz(Me.SearchDate, "") <> "" And Nz(Me.SearchTime, "") <> "" Then
    
    rs.FindFirst "[DateField] & [TimeField]='" & Me.SearchDate & Me.SearchTime & "'"
    
    If Not rs.NoMatch Then
     
      If Not rs.EOF Then Me.Bookmark = rs.Bookmark
    
    Else
    
     MsgBox "No Matching Record Found! You Will Now be Taken to a New Record"
     DoCmd.GoToRecord , , acNewRec
     Me.DateField = Me.SearchDate
     Me.TimeField = Me.SearchTime
     
    End If
   
   Else
    
    MsgBox "You Must Enter a Date and a Time In Order to Run a Search!"
   
   End If
  
End Sub

You'll have to replace DateField and TimeField with the actual names of your Fields. NB: Do not name these Fields Date and Time! Both of these are Reserved Words, and using them for names can lead to all kinds of problems! If you have already done so, take the time now to modify the names.

When the Command Button is clicked, the above code will search for the Date/Time combination that has been entered in the two search boxes.

If one or both of the search boxes are empty, a Messagebox will appear on the screen, advising the user to correct their oversight.

If a match for the Date/Time is found, the user will be taken to this Record.

If a no match for the Date/Time is found, the user will receive a Messagebox telling them that no match was found and that they will be taken to a New Record. They will go to a New Record and the Date and Time that was entered in the search boxes will now be automatically placed in the appropriate Textboxes on the New Record. If this behavior doesn't suit you, you can simply deleted the two lines

Me.DateField = Me.SearchDate
Me.TimeField = Me.SearchTime

from the code above.

As with most things in Access, there are other ways of doing this, but this is the approach I would take in addressing the task presented here.

Linq ;0)>

[TroxlSha]

Thank you tremendously, Missinglinq. This looks like a fix that will work. Although, (I am assuming), this shouldn't be an issue if I were to create the searchDate and searchTime fields onto the initial "switch board" form then tact the code to search on the command buttons leading to the respective form.

[Missinglinq]

...this shouldn't be an issue if I were to create the searchDate and searchTime fields onto the initial "switch board" form ...

Not sure exactly what you're asking, here, but searchDate and searchTime have to be on the Form that the Records are displayed on. Usually, for this sort of thing, they would be placed in the Header.

You could have them on a Menu Form, and then go to the Form they reside on and search, but that would require different code, and what would the advantage be? You still have to open the Form, regardless of where you place the search boxes.

Or maybe I've misunderstood your question.

Linq ;0)>