On click where conditions help (3 Viewers)

[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?

 

[Pat Hartman]

If there isn't a record you could add one there.

You never want to add an "empty" record and just leave it there no matter that you "intend" to immediately fill it with data. You always want some kind of validation in your form's BeforeUpdate event and with an "empty" record, there is nothing to validate but you are still entering invalid data into the database and allowing it to be saved.

The no code solution to open a form to a specific record or to a "new" record is to use the Open Form method with a where argument. If the record is found, the form opens to the requested record. Otherwise, it opens to a new, empty record. This means your form needs to be bound to a table or an unfiltered query which is not something I ever do since my BE's are almost always SQL Server and it is far more efficient to bind your forms to queries that contain selection criteria that severely limits the number of rows returned from the server.

 

[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.

 

[Pat Hartman]

Never said to add an "Empty" record.

Adding a record with no specific data and no fields validated is not good practice so I would never recommend it. Even when I copy existing records such as bids (most common type of data to actually copy), all the data is filled in and validated for the previous bid issued. This one has a new date and I also include a CopiedFromID, just to be clear how the record originated and so the user might copy bids other than the most recent and be able to tell what he copied from to make this Bid.

 

[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?