Record value from 3rd open form to table bound to 1st form (1 Viewer)

[Zydeceltico]

Hi All -

I have a mockup db attached. It represents our real world workflow needs.

There are two tables: tblCampingTrip and tblTripDetails.

tblCampingTrip has a field named MiscThoughts. How to populate that field from a 3rd open form is the subject of this post.

There are three forms that are always presented in this order: frmCampingTrip; frmTripDetails; frmGetMiscThoughts.

frmCampingTrip and frmTripDetails are both bound to their underlying tables and linked/related on CampingTrip_ID.

CampingTrip_ID is passed from frmCampingTrip to frmTripDetails via OpenArgs.

frmGetMiscThoughts is currently unbound. It has one unbound textbox on it: txtMiscThoughts.
Although it currently does not do anything, CampingTrip_ID is also passed to frmGetMiscThoughts via OpenArgs.

All forms are data entry = Yes.
frmTripDetails and frmGetMiscThoughts are pop-up and modal.
frmCampingTrip is open the entire time and is requeried after frmTripDetails has been saved.

The db opens to frmCampingTrip where some data is input and a command button is clicked to open frmTripDetails using this code:

Private Sub Command15_Click()
    Me.Dirty = False
    DoCmd.OpenForm "frmTripDetails", acNormal, , , , , CampingTrip_ID
End Sub

This records the data from frmCampingTrip into tblCampingTrip and opens frmTripDetails in order to enter related data into tblTripDetails. You'll notice the "Save/Exit" button on frmTripDetails is not enabled at this point because the user must also open frmGetMiscThoughts and add a note there. So the user clicks the "Enter Misc Thoughts" button and frmGetMiscThoughts opens.

There is a single unbound field on frmGetMiscThoughts named txtMiscThoughts. I want the value the user types in this box to be recorded in the field MiscThoughts in tblCampingTrip.

Right now, entering data and clicking Save and Exit results in nothing.

I believe that at this point the record from frmCampingTrip is already saved in tblCampingTrip.

There is a "Save and Exit" button on frmGetMiscThoughts.

Would an append query attached to that command button's On Click event record the value in txtMiscThoughts to tblCampingTrip?

Is there a better way to accomplish this given 1) subforms are out of the question at this point and 2) this really does exactly represent our real world workflow.

Thanks a bunch,

Tim

 

[Ranman256]

you must put in the full [field]=value in the correct param :

DoCmd.OpenForm "frmTripDetails", , ,"[CampTripID]=" & me.txtBoxName

 

[Zydeceltico]

you must put in the full [field]=value in the correct param :

DoCmd.OpenForm "frmTripDetails", , ,"[CampTripID]=" & me.txtBoxName

Thanks for the advice Ranman. But that portion of the workflow already works really well. If I take frmGetMiscThoughts out of the equation datat from both the other forms is being recorded correctly to their underlying tables....so no problem there.

The challenge is getting new data from frmMiscThoughts to record to field MiscThoughts in tblCampingTrip as additional data in the record of tblCampingTrip that was recorded when I opened frmTripDetails.

 

[jdraw]

I can get the MiscThoughts to populate in tblCampingTrip but it seems convoluted. The logic seems to jump over tripDetails for some reason????
Perhaps there is a design issue to be resolved??

I added this to the proc on frmMiscThoughts

Private Sub cmdSaveAndExit_Click()
CurrentDb.Execute "Update tblCampingTrip Set MiscThoughts = '" & Me.txtMiscThoughts & "'  where " _
    & " CampingTrip_ID =" & Me.tempCampingTrip_ID & ";", dbFailOnError
    .....

tblCampingTrip

CampingTrip_ID	Location	WithWhom	MiscThoughts
22​	Odessa	Jim
23​	Nepean	Bob
24​	Freemont	Hank
25​	New York City	Terry	Terry is on vacation

Perhaps we could go back to the original database and describe what this represents in simple English.

 

[Zydeceltico]

I can get the MiscThoughts to populate in tblCampingTrip but it seems convoluted. The logic seems to jump over tripDetails for some reason????
Perhaps there is a design issue to be resolved??

Thanks jdraw - - that works exactly as I had hoped. I always forget about that very useful line of code.

Perhaps we could go back to the original database and describe what this represents in simple English.

What this represents is:
frmCampingTrip = frmInspectionEvent
frmTripDetails = any given type of inspection form (each bound to a different underlying table)
frmGetMiscThoughts = a popup form to enter general notes and a hyperlink address that points to job specific folders on our network drive where the inspector can add photos on the fly.

We don't always need notes photos during an inspection and could be well in to an inspection before realizing that we should take photos - - and I want the photos and notes recorded in one place in tblInspectionEvents rather than having redundant notes and photo fields across all of the inspection tables.

After notes and photos are taken, I want the inspector to go back to the inspection form (frmTripDetails) to finish out the inspection.

 

[jdraw]

So, somewhere in each Inspection type processing/record collection, you need the ability to record something including the inspection type and ID OR skip over that area.

 

[Zydeceltico]

So, somewhere in each Inspection type processing/record collection, you need the ability to record something including the inspection type and ID OR skip over that area.

Exactly. Operative phrase: "the ability." Not always required. But when it IS required needs to be immediately accessible. And it needs to relate to InspectionEvent_PK.

 

[Zydeceltico]

So, somewhere in each Inspection type processing/record collection, you need the ability to record something including the inspection type and ID OR skip over that area.

Here's a related question:
I have added another table and form: tblDifferentStuff and frmDifferentStuff.

frmDifferentStuff can be opened from frmCampingTrip as a choice to use instead of using frmTripDetails.

And then the process is intended to be the same as you helped me with above. Namely frmDifferentStuff also has a button on it to open frmGetMiscThoughts.

All this works so far - but I added a line of code to the buttons that open frmTripDetails and frmDifferentStuff with their "Save and Exit" buttons disabled. This is on purpose. I want the user to at least open frmGetMiscThoughts before saving the data on frmTripDetails or frmDifferentStuff.

The idea being that when the data on frmGetMiscThoughts is saved and that form closes, the user is returned to frmTripDetails OR frmDifferentStuff - - whichever one has been opened - - at which time the "Save and Exit" button would then be enabled.

I added this code to the "Save and Exit" button on frmGetMiscThoughts:

Private Sub cmdSaveAndExit_Click()
    CurrentDb.Execute "Update tblCampingTrip Set MiscThoughts = '" & Me.txtMiscThoughts & "'  where " _
    & " CampingTrip_ID =" & Me.tempCampingTrip_ID & ";", dbFailOnError
    DoCmd.Close '...this form (frmGetMiscThoughts) closes
                ' and user is returned to frmTripDetails
    Forms!frmTripDetails.cmdSaveExit.Enabled = True ' now that misc thoughts have been entered_
                                                    ' data on the prior form can be saved. BUT_
                                                    ' the code references a specific form.
                                                   
End Sub

What this means is I currently can only use this code on the Save and Exit button of frmGetMiscThoughts for frmTripDetails.

What I need is for this code of line:

Forms!frmTripDetails.cmdSaveExit.Enabled = True

to read more like this:

Forms!THE NAME OF WHATEVER FORM OPENED frmGetMiscThoughts.cmdSaveExit.Enabled = True

I suspect that I could use a Public variable like strFormName to carry a variable and I've been playing with it but failing repeatedly.

Thoughts?

I've attached a new db mockup to this post.

Thanks,

Tim

 

[jdraw]

I don't follow the logic necessarily, but I've attached a zip with a revised version that I works as I understand your requirement.
I added a variable in module1. It gets valued when button is clicked on frmCampingTrip.
Revised the code in frmGetMiscThoughts to identify and process which form's Save and Exit should be enabled.

 

[Zydeceltico]

I don't follow the logic necessarily, but I've attached a zip with a revised version that I works as I understand your requirement.
I added a variable in module1. It gets valued when button is clicked on frmCampingTrip.
Revised the code in frmGetMiscThoughts to identify and process which form's Save and Exit should be enabled.

Thanks Jdraw - works exactly as I had hoped!

Sorry its been a few days. Work scheduling with this virus deal is nuts. I was just off four days but back on now for four tens.

So: learning for Tim: I'm now studying your "CurrentDB.Execute" statement and also SELECT CASE.

 

[jdraw]

As I have said - covid-19 has us all "grounded" in some manner. I have some time to "test" a few things. As I said in recent post, I didn't understand your logic, but worked out a scheme to get your expected result. Glad it works for you.
""A question though:
Could you go to the third form first, then come to the middle form for details? It seemed strange to go to form2, then 3, then close 3, then 2, then 1?? You know your situation better than readers, so if it makes sense to you--then it meets your needs.

 

[Zydeceltico]

""A question though:
Could you go to the third form first, then come to the middle form for details? It seemed strange to go to form2, then 3, then close 3, then 2, then 1?? You know your situation better than readers, so if it makes sense to you--then it meets your needs.

Sometimes - actually very often - we could go to form3 second - or even first. It is the outlier and unpredictable real world circumstances that make it necessary to go through the forms for data collection in the manner I described and that you helped me solve.

In a typical non-eventful operation on any given workstation, the flow you described would be just fine - and appropriate. But it is the outlier, unexpected conditions that are most important to capture accurately which is when the data on form 3 is super-critical to get (i.e., notes, photos, other extraneous, non-typical inspection data). That data does not always present itself until later in an inspection. By presenting the forms the way I have,the inspector can 1) run a full normal inspection and 2) has the flexibility to go to form 3 at any given time during an inspection - if need even be.

The travel back to form 1 means that an inspection is complete and form 1 requeries to begin a new inspection. Form 1 is where date/time, job number, operator and inspector are recorded. Little else. At least via Form 1.

 

[jdraw]

10-4 If it makes sense in your environment -all is well. Sometimes an independent set of eyes will ask a question, and sometimes it sparks or confirms an approach!

 

[Zydeceltico]

The travel back to form 1 means that an inspection is complete and form 1 requeries to begin a new inspection. Form 1 is where date/time, job number, operator and inspector are recorded. Little else. At least via Form 1.

I should mention that the table bound to Form 1 is the same table where notes and photos from Form 3 are stored. My primary reasoning for this is that we have a few different ongoing material and process issues that present across multiple operations (e.g., oil canning may manifest during rolling, welding, or even painting). I give the inspector an opportunity on form 3 to answer these two questions with a yes or no: "Was oil canning present?" and "Were any coating issues recognized?" These are our two main, major, and ongoing issues. By recording the answer to those two general questions in this table, I have a very immediate means of querying and reporting across ALL various types of inspections for any given job - which is the main and primary purpose of this database.

 

[Zydeceltico]

10-4 If it makes sense in your environment -all is well. Sometimes an independent set of eyes will ask a question, and sometimes it sparks or confirms an approach!

And your and others on this forum independent sets of eyes are truly why this db is finally working properly for us!

Thank you as always!

 

[Zydeceltico]

...and sometimes it sparks ........an approach!

A spark has caught!

WIth this code:

    CurrentDb.Execute "Update tblCampingTrip Set MiscThoughts = '" & Me.txtMiscThoughts & "'  where " _
    & " CampingTrip_ID =" & Me.tempCampingTrip_ID & ";", dbFailOnError

...........if I wanted to update mutiple fields would I just repeat the entire block of code for each field I want to update or is their a succinct way to update multiple fields within the same "CurrentDb.Execute" statement?

The number of fields to update would remain static and would always be the same.

Ahhhh...............the spark is quickly becoming a small fire!

Thanks,

Tim

 

[jdraw]

Yes, as long as the fields are in the same table, and the values are available.

However, a better approach than direct SQL string in the Currentdb.Execute is to identify the SQL in a separate string variable. Then substitute the variable name in the Currentdb.Execute. The rationale for this is: you can let Access evaluate the SQL and see if there are errors before actually executing the statement.

Here's an example

...
Dim updSQLx As String
50     updSQLx = "UPDATE tbl_Tracks INNER JOIN tbl_Programmes ON" _
       & " tbl_Tracks.ArtistLink = tbl_Programmes.ArtistLink " _
       & " SET isavailable = true" _
       & " WHERE (((tbl_Programmes.PlayedDate)<DateAdd('m',-3,Date())));"
60    'CurrentDb.Execute updSQLx, dbFailOnError
       Debug.Print updSQLx ' <<<<<<<<<<<<<<<<<<<<<<<<<
......

and the Debug.print shows

UPDATE tbl_Tracks INNER JOIN tbl_Programmes ON tbl_Tracks.ArtistLink = tbl_Programmes.ArtistLink  SET isavailable = true WHERE (((tbl_Programmes.PlayedDate)<DateAdd('m',-3,Date())));

 

[Zydeceltico]

Yes, as long as the fields are in the same table, and the values are available.

However, a better approach than direct SQL string in the Currentdb.Execute is to identify the SQL in a separate string variable. Then substitute the variable name in the Currentdb.Execute. The rationale for this is: you can let Access evaluate the SQL and see if there are errors before actually executing the statement.

Here's an example

...
Dim updSQLx As String
50     updSQLx = "UPDATE tbl_Tracks INNER JOIN tbl_Programmes ON" _
       & " tbl_Tracks.ArtistLink = tbl_Programmes.ArtistLink " _
       & " SET isavailable = true" _
       & " WHERE (((tbl_Programmes.PlayedDate)<DateAdd('m',-3,Date())));"
60    'CurrentDb.Execute updSQLx, dbFailOnError
       Debug.Print updSQLx ' <<<<<<<<<<<<<<<<<<<<<<<<<
......

and the Debug.print shows

UPDATE tbl_Tracks INNER JOIN tbl_Programmes ON tbl_Tracks.ArtistLink = tbl_Programmes.ArtistLink  SET isavailable = true WHERE (((tbl_Programmes.PlayedDate)<DateAdd('m',-3,Date())));

I'm working through it. Went down a bit of a rabbit hole with the "INNER JOIN" bit of your code above. I don't think that is pertinent to my situation as all of the various fields I'll be updating are all in the same table.

So now I'm looking for examples of wrapping a SQL statement with a single table updating multiple fields into a access variable.

I think I need new glasses - going blind on this end - that - or reading SQL statemets for the past 2 hours has me........in need of a walk.

 

[jdraw]

Yes, the inner join was not a great example. It was the updSQLx and assigning it the SQL statement that was important. I'll get a better example. The thing to be aware of is that there are some pieces that are just text and some that require Access to get the value of a control/field and put that value into the SQL string.

1)
msql = "Update tblMainRecordsForAssignment " _
& " SET User = '" & Me.Combo2.Column(1) & "'" _
& " WHERE User Is Null"

2)
Dim SQL As String
SQL = "Update Dogtest Set Sire =" & Me.cboSire _
& " Where dogId = " & Me.cboDog

3)
Dim SQL As String
SQL = "Update TutorAvailability SET IsAvailable = False " _
& "WHERE Name = '" & Me.ListAvail.Column(0) & "' AND " _
& " UniClassname ='" & Me.ListAvail.Column(2) & "' AND " _
& " IsAvailable = True "

 

[Zydeceltico]

Yes, the inner join was not a great example. It was the updSQLx and assigning it the SQL statement that was important. I'll get a better example. The thing to be aware of is that there are some pieces that are just text and some that require Access to get the value of a control/field and put that value into the SQL string.

1)
msql = "Update tblMainRecordsForAssignment " _
& " SET User = '" & Me.Combo2.Column(1) & "'" _
& " WHERE User Is Null"

2)
Dim SQL As String
SQL = "Update Dogtest Set Sire =" & Me.cboSire _
& " Where dogId = " & Me.cboDog

3)
Dim SQL As String
SQL = "Update TutorAvailability SET IsAvailable = False " _
& "WHERE Name = '" & Me.ListAvail.Column(0) & "' AND " _
& " UniClassname ='" & Me.ListAvail.Column(2) & "' AND " _
& " IsAvailable = True "

Thanks. That really helps - especially considering the major minefield of songle and double quotations! That really helps. Thanks.