Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations sizbut on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

NO One will help me w/ this Query question

Status
Not open for further replies.

TheCleaner

Technical User
Apr 21, 2001
19
US
Sorry to seem so uptight about this. But I've asked some questions on this board and nobody replies. I've fumbled my way through alot of stuff, but this I just can not get. So here goes, last time. I have a form that I use to imput duty log times. With this form I use the [Pilot]drop down field. A query then finds this pliots last duty date/time and places it on the form for all to see. However I have a requery take place when the user selects and changes, it updates as well. Now the question. On saving I want the value that was suplied from that query in the LastDuty list box to be stored in a table, for yet another calculation to take place for error messages and reports alike. PLEASE any help! Thank you in advance
 
insufficien info to provide a detailed response.

to save the value of a bound control in a talbe other than the one the form is bound to, you need to save the value in a variable and create a seperate procedure to save it again (elsewhere). Other info which accompanies the datum needs to likewise be saved in variables and 'manually ' (programattically) included.


MichaelRed
redmsp@erols.com

There is never time to do it right but there is always time to do it over
 
Thank you for taking the time to answer. The short of it goes like this. How do I get the results of a query(requery) that are displayed on my form saved into a table when I save/exit the record(form). Here we go. Form "timeLog" has field [PilotOnDuty]. When you select the pilot via drop down it promps a requery. The query sorts that [PilotOnDuty]name, finds the the top value in [Record], with [Status] having a value of "2". That gives me the latest record of that pilot and that pilot/record's [EndTime]. That value I get to display in the form in [LastDutyTime]. Now the user is going to place a value in this new record's [StartTime]. I need it then to do something like this "=GetElapsedTime([LastDutyTime]-[StartTime])" for that form/record. The value will then be the [TotalRestTime]. I need this value to be stored (1)if it's less than 10hrs a error message will be displayed (2)to be shown in reports. I have the code, if I dare call it that, for all the math. Its just getting it to store those values, and store them were I want. Thanks in advance sorry so long winded.
 
Still not clear to me. For &quot;Bouind Forms&quot; (e.g. form.recordsource <> &quot;&quot;), all bound controls (e.g. Control.Source <> &quot;&quot;), the control value is automatically save in the RecordSource.ControlSource when ever the recordpointer is moved (e.g. a different record id selected or the form is closed). For ALL OTHER SITUATUIONS, you must explicitly save the value of the control to a recordset either by the &quot;.AddNew&quot; method or the &quot;.Edit&quot; method. This implies that you need to declare (Dim xxx As RecordSet)and Instantiate (Set xxx = db?.OpenRecordset(&quot;recordsetname&quot;) the recordset, move the record pointer to the appropiate record (xxx.[FindFirst | Seek] Criteria ... (check &quot;noMatch for versions before 2K or EOF for 2K) and assign the field to the value of the control (xxx![timeoff = myfield)

Almosst certainly NOT complete, but this SHOULD get you within a few errors of the process.


MichaelRed
redmsp@erols.com

There is never time to do it right but there is always time to do it over
 
Wow, I was with you up until the &quot;declare&quot;. Let me try it this way. Form has field [LastDuty] who's control source is &quot;LastDuty&quot;. It's Row source is a query, SELECT DISTINCTROW [QueryName].[FieldInQuery] From [QueryName];

The query has a perameter(Froms!FormName!FieldOnForm!). So, I had to promt an AfterUpdate requery on FieldOnForm updating the data in the &quot;LastDuty&quot; list box. The query is finding the value of an old record ([EndTime]) based on what name I place in FieldOnForm.
Private Sub Field_On_Form AfterUpadate()
'Requery LastDuty list box.
Me!LastDuty.requery
End Sub

Now when I save and exit the form I want the value thats in the &quot;LastDuty&quot; list box (coming from the query, based on a value that's on the form)to be placed in a table. From what I understand if the ControlSource property is an expression it is a value that is displayed/read-only, not stored...I want to store it. How can I make that happen.

Please pardon my ignorance. And Thanks again for taking the time to answer my question (again). The More BigBird / CookieMonster style the answer, the better. For what it's worth you and your knowledge are highly reguarded, obviously, your one of the Forum's Top Experts. Thanks you.
 
Mind if I but in?
It's not easy to understand what &quot;TheCleaner&quot; is trying to do, and I think that is because he/she wants to store calculated values, when all he/she needs is start and stop times and dates.
e.g., &quot;Sky King&quot; flew on &quot;01-May-01&quot; from &quot;0830&quot; to &quot;1300&quot;
.......&quot;Smiley&quot; flew on &quot;02-May-01&quot; from &quot;1115&quot; to &quot;1600&quot;.
Being a recent convert to NEVER storing calculated values, I know it's better to recalculate the times that the pilots were in the air, how long ago, and when can they legally fly again. The required information can be recaluculated at any time and displayed in report(s) and form(s).
Please excuse me if I've missed the point, but it's pretty well disguised.
:) Gus Brunston
An old PICKer
padregus@home.com
 
OKAY. I'm sorry for all this, and I completely understand what Gus is saying. Forget ALL that I've said. This is the new question. I have a sort query that finds a field in an old record. How can I save the value that it found? I may be thinking this is an easier task than it is, or maybe to many variables come into play. Any help will be another step closer. Thanks J
 

If you want to store the value in some table other than the current form's &quot;Record Source&quot;, I can't help; I'm sure it can be done, but I just never do it.

But, if in the current form's &quot;Record Source&quot;, then as soon as you &quot;tab out&quot; or &quot;exit&quot; the current record, I can't understand why the data in each bound control is not saved. If you want to save the data without leaving the current record, insert a command button and follow the wizard's instructions for saving the record.

And maybe this process is just too advanced for me... Gus Brunston
An old PICKer
padregus@home.com
 
thanks everyone for all of your help. I got it figured out.
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top