Saving records

[Dave E]

OK Chaps, and Chapesses

I have a form with an underlying table containg many records.
Just lately, I was thinking of adding a couple of new fields which exist only once in a table with two fields.

These fields contain the dates when an action needs to be triggered, which are generated, but not stored, in the maintable for the reason stated above.

Is there a simple way to store two records in a separate table, using something like a 'reverse' DlookUp? A DPut("[record]","[Field]","

") is what I need rather than creating a recordset or update query. I don't want to add a record but always replace the previous dates.

Any thoughts? This isn't a critical exercise for me, more an exercise in coding a simple function.

 

[CJ_London]

Domain functions still use a recordset, just not available to view. Although possible they can refer to the tabledef for some cases if there is no criteria

You can write your own dput function - not clear what it would look like since not clear what you are trying to do - implication is an append

 

[plog]

I think I speak for most of us when I say, 'Huh?'

I was thinking of adding a couple of new fields which exist only once in a table with two fields.

These fields contain the dates when an action needs to be triggered, which are generated, but not stored, in the maintable for the reason stated above.

I just can't parse what is happening or what you want to happen. Perhaps you can demonstrate your issue by showing us your current table structure along, your proprosed table structure and some data that demonstrates what you hope to accomplish.

 

[Pat Hartman]

If you are asking if there is an update function that corresponds to dLookup(), the answer is no.

Here's a list of ALL available functions. You might want to keep it handy. It is very useful since it is grouped by category rather than being straight alpha. https://support.microsoft.com/en-us...category-b8b136c3-2716-4d39-94a2-658ce330ed83

Just create a table to hold the dates. Then create a UDF function to update the table. Call the UDF when you want to change the values.

 

[Dave E]

If you are asking if there is an update function that corresponds to dLookup(), the answer is no.

Here's a list of ALL available functions. You might want to keep it handy. It is very useful since it is grouped by category rather than being straight alpha. https://support.microsoft.com/en-us...category-b8b136c3-2716-4d39-94a2-658ce330ed83

Just create a table to hold the dates. Then create a UDF function to update the table. Call the UDF when you want to change the values.

It was a rather clumsy attempt at a question, having read it back.

But what you have suggested looks like what I want. I'll take a browse thru the list and, as you suggest, keep it for future reference.

Thanks for that.

Dave E

 

[Dave E]

I think I speak for most of us when I say, 'Huh?'



I just can't parse what is happening or what you want to happen. Perhaps you can demonstrate your issue by showing us your current table structure along, your proprosed table structure and some data that demonstrates what you hope to accomplish.

Yes, it was a befuddled question, and it deserved your response. Basically, I have a new table with 2 fields, both containing dates. One for Reminders and one for a ToDo list. The two fields hold the dates for the next date action required for the monthly/seasonal work required in the garden and the other for a nudge about things to do...

There are two unbound controls on the main form with ReminderDate and TodoDate. For the Reminders - When the main form opens, the OnOpen code checks the 'ReminderDate' to see if it is due (Date >= ReminderDate) and, if it is, it unhides a button suggesting that the user might want to use the button to open the Reminders form, where the Reminders are noted, and the next 28 days can be added to the existing ReminderDate and stored in the same field as the old ReminderDate in a separate table for the next 28 days.

The question was whether there was a way to get the new ReminderDate into the separate table to replace the old ReminderDate in a similar fashion to the DLookUp() function, in reverse i.e. put the values back to the table rather than pulling them out.

The 'Todo' code and methodology are the same as the above.

I hope you are now able to 'parse' what I was trying to. Apologies for my lack of clarity.
Pat Hartman seems to have offered a source which I'm off to explore.

Thanks for you input.

Dave E

 

[Pat Hartman]

Pat Hartman seems to have offered a source which I'm off to explore.

I have a lot of experience reading and understanding questions from confused newbees. I'm glad I got the gist of your delima

Just ask if you need help with the function. Post the table and column names so we can at least try to get something useful.

 

[LarryE]

OK Chaps, and Chapesses

I have a form with an underlying table containg many records.
Just lately, I was thinking of adding a couple of new fields which exist only once in a table with two fields.

These fields contain the dates when an action needs to be triggered, which are generated, but not stored, in the maintable for the reason stated above.

Is there a simple way to store two records in a separate table, using something like a 'reverse' DlookUp? A DPut("[record]","[Field]","
") is what I need rather than creating a recordset or update query. I don't want to add a record but always replace the previous dates.

Any thoughts? This isn't a critical exercise for me, more an exercise in coding a simple function.

It sounds to me like you are wanting to use an UPDATE action query where you update (not append) a field (or fields) in a table based upon some given criteria date. The criteria date could be a control on a form or an input window that pops up. You can also use an SQL UPDATE statement. When you stated you didn't want to use an UPDATE query, I think you meant you didn't want to use an APPEND action query.

Create and run an update query - Microsoft Support

Use an update query in Access desktop databases to update or change the existing data in a set of records.

support.microsoft.com

W3Schools.com

W3Schools offers free online tutorials, references and exercises in all the major languages of the web. Covering popular subjects like HTML, CSS, JavaScript, Python, SQL, Java, and many, many more.

www.w3schools.com

 

[Dave E]

It sounds to me like you are wanting to use an UPDATE action query where you update (not append) a field (or fields) in a table based upon some given criteria date. The criteria date could be a control on a form or an input window that pops up. You can also use an SQL UPDATE statement. When you stated you didn't want to use an UPDATE query, I think you meant you didn't want to use an APPEND action query.

Create and run an update query - Microsoft Support

Use an update query in Access desktop databases to update or change the existing data in a set of records.

support.microsoft.com

W3Schools.com

W3Schools offers free online tutorials, references and exercises in all the major languages of the web. Covering popular subjects like HTML, CSS, JavaScript, Python, SQL, Java, and many, many more.

www.w3schools.com

Yes. It looks like either a function, which I'm going to try, or an UPDATE query and see which one fits my purpose. It seems odd that it's possible to use a function like DLookUp to get data from a table but not be able have a DPut function to put data into a table. Still, now I see there isn't one, I'm happy to proceed.

Thanks

Dave E

 

[Dave E]

I have a lot of experience reading and understanding questions from confused newbees. I'm glad I got the gist of your delima

Just ask if you need help with the function. Post the table and column names so we can at least try to get something useful.

I knew that you'd pop up with a definitive reply. The comment was a tad tangled but I wasn't, nor am I a newbie, having written many database programs for my uni and personal use, such as the one that keeps me occupied during my retirement.

I will go ahead, now, and try out a function and an UPDATE query and see which one suits the task.

Thanks

Dave E

 

[Dave E]

Domain functions still use a recordset, just not available to view. Although possible they can refer to the tabledef for some cases if there is no criteria

You can write your own dput function - not clear what it would look like since not clear what you are trying to do - implication is an append

Thanks. I will write a DPut function and try an UPDATE function to see which is best for me.

Dave E

 

[Pat Hartman]

If you are going to do something once, you can just use an update query. If you are going to do the update from multiple places, that is when you would create a function. Then if you ever need to change how this works, you have one function to change instead of the same code in multiple places.

 

[Dave E]

Yes Pat. Thanks. I have written the code for two forms that take and put data into a table. It works well, and I was content to write the code knowing there wasn't a pre-existing function available. It does seem odd that one doesn't already exist in Access when the DLookUp function is commonly used but its counter-part doesn't exist. Life is full of anomalies.

 

[Pat Hartman]

It does seem odd that one doesn't already exist in Access when the DLookUp function is commonly used but its counter-part doesn't exist. Life is full of anomalies.

I think you have a unique situation. I've been using Access for 30 years and never saw a need for a function to update a single field.

Another solution would be to have a bound form that you open when the database opens. anytime you need this particular data, you get it from the bound form which is open but not visible. Anytime you want to change it you update the value in the bound form. When the application closes, the bound form will automatically save the record and close as part of the shut down process. I've done this with security information. It loads as part of the login process. When the login completes, the login form opens the main menu and hides itself. I never had a need to update the security data though. It was just used for reference.

 

[The_Doc_Man]

Yes Pat. Thanks. I have written the code for two forms that take and put data into a table. It works well, and I was content to write the code knowing there wasn't a pre-existing function available. It does seem odd that one doesn't already exist in Access when the DLookUp function is commonly used but its counter-part doesn't exist. Life is full of anomalies.

Just as a comment that might help you here... Strictly speaking, unless you use some sort of .Execute option on an Update or Insert Into query and then check the .RecordsAffected property to see how many records you updated, you have no idea if you updated anything ... unless you go back after-the-fact to test whether something changed. But the problem is more complex than that, since with a poor criteria section, you might update MORE than one record.

Part of the problem (and potentially related to WHY there is no DPUT function) is that all of the Dxxx functions are based on an implied SELECT. You could IMAGINE that DLookup( field, domain, criteria) would be executed by building an SQL statement to open a recordset using

DSelectString = "SELECT " & field & " FROM " & domain & " WHERE " & criteria & " ;"

I'm not saying that is how it is done, but you could imagine it to be somewhat similar to this. But here is the crucial part: A SELECT query returns a recordset, even if it is the empty set. An UPDATE or INSERT INTO does not return ANYTHING. Not an empty set... but no return at all. So you have no feedback inherent in that function. With no feedback, the question becomes "What would a DPut function return?" I have MY answer but that has nothing to do with Microsoft's ruminations on functions.

 

[Dave E]

I simply wrote a function that opens a recordset for one table, one record, with two fields and the same unique ID. When used, a new date is saved to the table as in -

MySet.Edit
MySet![ReminderDate] = Forms!MyForm!ReminderDate
MySet.Update

It works. I have set up two fields on the main form which reflects the changes to the table. You have to bear in mind that this is a database for my entertainment and personal use as well as a way of keeping the brain active. I only ever ask for help or superior knowledge when I think I've exhausted all of my own attempts.
I am always greatful for help when I need it.

Thanks

Dave E

 

[gemma-the-husky]

It was a rather clumsy attempt at a question, having read it back.

But what you have suggested looks like what I want. I'll take a browse thru the list and, as you suggest, keep it for future reference.

Thanks for that.

Dave E

I imagine one of the reasons there is no reverse dlookup is the problem of dealing with non-unique responses. Dlookup returns a random or maybe first instance of a successful search. Therefore a corresponding dput would not know which record to update, and needs to be stated in a different manner.

@The_Doc_Man touched on this above.

 

[Dave E]

In this case the Date is generated, not selected or input by a user. The code detects when a set period has elapsed and then recalculates the new one as a DateAdd('d', 30, Date). That is then saved to the holding table and used by the relevant form's OnOpen routine which looks at the saved date and reacts accordingly.

For me a DPut would have worked well in a similar way as the DLookUp function. The IsNothing() function is another non-standard function/routine that has served me well for many years and could be include in Access's function set. My only regret is that I don't remember who wrote that back in the 90s.

 

[Isaac]

The IsNothing() function is another non-standard function/routine that has served me well for many years and could be include in Access's function set.

What would you want it to do, in practical terms/example?

 

[CJ_London]

IsNothing sounds like excels isblank.

Excel does not have datatyping, databases do.