On click where conditions help

[mberry071]

Trying to set up a on click command button on form1 that will open to a record in form2 based on matching date fields on form1 and form2.

I would also like if there is no match then it opens to a new record in form2. Also like the ability to still go back to previous records in form2 without having to press the filter. I can't seem to get the order correct using vba.

Form1 is my main menu with a =date() field. Once and employ creates form2 for the current day I'd like other employees to be able to click the button and go straight to the current days record but if that record doesn't exist yet I'd like a new record to appear. There will never be two records in form2 with the same date.

 

[theDBguy]

Hi. Welcome to AWF!

One approach is to pass the record ID or whatever criteria you're using to the OpenArgs property of the second form. Then, to navigate to that record in Form2, you can use the same approach that the Combobox Wizard uses when you select the third option.

 

[The_Doc_Man]

My initial advice based on your description of behavior for Form2 is that you put some code the Form_Load routine to obtain the date, then use the Me.RecordsetClone to find a matching record (if it exists). If it does, you can copy Me.RecordsetClone.Bookmark to Me.Recordset.Bookmark to jump to that record. Otherwise (if .NoMatch is true after that date search) you can do something with DoCmd.GoToRecord for the acNewRecord option.

DoCmd.GoToRecord method (Access)

Office VBA reference topic

learn.microsoft.com

Or, instead of using the bookmark, there is a way to use GoToRecord to select a particular record. (See the link for that option, too.)

I don't see anything that you need to do from Form1 other than launch Form2.

 

[Mark_]

Do you have code on Form1 that checks for the existence of the current days record?
If not, you could set up Form1 to check when it opens. If there isn't a record you could add one there.
Much of this depends on if you need to know every day the system is started VS you only want records in the table Form2 deals with WHEN users go in to it.

More important, how do you keep two users from trying to update this record at the same time? Standard example is user 1 goes in and gets distracted. User 2 goes in and updates. What should happen when user 1 finally gets back to the form and tries to do their update?

 

[Mark_]

Never said to add an "Empty" record.

In some cases, adding a record that indicates "X" started the application on date "Y" is required for other use, such as showing "X" opened the store on "DATE", even if there ends up being no business on "DATE". Once OP indicates if this is something needed even if other's don't update it, then OP can decide if this is an approach needed to meet their business rules.

 

[Isaac]

Add code to open form2 and then add code to set it's .Recordsource property using a SQL statement.
Run a Dcount first to see if the query would produce records, and if not, go to a new record in the new form.

code wise what have you tried so far?