SHOW VALUES FROM DIFFERENT TABLES IN ONE FORM

[cenderawasih]

Hi.....

I have a form that is bound to a table...let say table A. Table A contains only the current budget data. The previous budget data are moved into a history table. What I need is for the form to display all the current budget information plus the one field that contains the previous budget value in the history table.......

I cannot do a query to select the previous budget value field from the history table and bound my form to the query because there are so many codes in my form that refer to table A.

I also cannot use the DLookUp command because to get the budget value the Job Number and the subjob number from both tables must match........

Can anyone gives some idea how to solve my problem?

 

[SeeThru]

You need a form that is NOT bound to a table.

At the time you need to see the data, you can either open the recordset in the background and individually update each control - that will give a display only form - or you can bind the table to a query in code. Not only can you set the correct table, you can filter the data to speed up access and ensure the user only sees the right date.

 

[TonyJollans]

Hi cenderawasih,

I don't see why you can't use a DLookup, it's not restricted to a single condition ..

DLookup("[PreviousValue]","[HistoryTable]","[JobNumber]=" & [JobNumber] & " AND [SubJobNumber]=" & [SubJobNumber])

Enjoy,
Tony

------------------------------------------------------------------------------------------------------
We want to help you; help us to do it by reading FAQ222-2244 before you ask a question.

 

[Rolliee]

cenderawasih,

It is just a matter of opening the 'foreign' table and do what you want. Use a button or an event to do this - perhaps on_current to see it in every record of your main form.

startcode:::
DIm rs as dao.recordset, numof, ss, TestID

set rs = currentdb.openrecordset("ForeignTable&quot

rs.movelast
rs.movefirst
do while not rs.eof
if rs("someField&quot = ss then
numof = numof + rs("Someotherfield&quot
endif
' all sorts of other stuff


loop

me.txtField2 = ss
me.txtField2 = numof
rs.close
set rs = nothing

exit sub
::::: endcode


rollie@bwsys.net

 

[Rolliee]

Hold Everything!!!! We have an infinite loop. It needs a

rs.movenext just above the "loop" command.

I've done that more than once in code. In 1972 one such loops cost my company CAT Inc $2500 in computer time to USSteel.

Rollie E

 

[cenderawasih]

Hello SeeThru, TonyJollans and Rolliee.......

Thank you for your ideas and help.......

TonyJollans....thanks for the info....all this time i thought there's only can be one condition in a DLookUp command....you've really made my work becomes easier...
but anyway...I still cannot use the DLookUp commands for my form because my form is a continuous form....and the text box that have the DLookUp command (which of course an unbound textbox) will display the value for the 1st record in the table that matches the DLookUp condition for all records that are displayed in the continuous form.
meaning....all records in the continuous form will have the same Previous Budget Value....

And Rolliee....thank you for the codes...The codes works fine but it also have the same problem like the DLookUP,
The unbound textbox will only shows 1 value from the History table (the 1st record that matches the criteria) for all records in my continuous form........

Is there a way to handle this unbound field problem?

 

[Rolliee]

Let the unbound textbox be concatenated.

me.text0 = rs.fields(0) & " " & rs.fields(1) & " " & rs.fields(2)

This would show the contents of the first threee fields in rs in the textbox.

rollie@bwsys.net

 

[cenderawasih]

Thanks all....I'm using the query method to solve this problem and change all my codings to refer to this query. But another problem occur:

I have a problem to refresh my form. Here is my codes:

'This is the coding in a dialog box named "FrmChangeData"
'on button 'btnUpdate'
'User will enter new values in this dialog box...then when
'the 'btnUpdate' button is clicked these values
'will be moved into a subform of a main form that
'named 'FrmBudget'.
'FrmChangeData is an unbound form but the subform that the
'data from here will be moved into (that is the 'FrmBudget'
'form) is bound to a Query from 2 different
'tables.....'TblBudget' and 'TblTempLastOfHistory'


Private Sub btnUpdate_Click()

'Append the selected record into the history table
DoCmd.OpenQuery "QryAppHistory"

'Delete the temporary table that contains Last record of
'History data
DoCmd.OpenQuery "QryDeltblLastOfHistory"

'Append the Last records of History data into the temporary
'table "tblTempLastOfHistory"
DoCmd.OpenQuery "QryGetLastOfHistory"

'Me=dialog box that is 'FrmChangeData'
Forms!FrmMain!FrmBudget!NewValue = Me.txtChange.Value

'Requery the field where the value has been changed
Forms!FrmMain!FrmBudget!NewValue.Requery

'Requery the field where the value has been changed
Forms!FrmMain!FrmBudget!PrevValue.Requery

DoCmd.Save acForm, "FrmBudget"
DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, , acMenuVer70
Forms!FrmMain!FrmBudget.Requery


DoCmd.Close acForm, "FrmChangeData"

End Sub

My problem is....when i click on btnUpdate this error msg appear...

"YOU MUST SAVE THE CURRENT FIELD BEFORE YOU RUN THE REQUERY ACTION"

and highlighted on : Forms!FrmMain!FrmBudget.Requery

If u notice in my codes...I have insert the save record command, but this error message keeps
coming......Can anyone help...?????

 

[cenderawasih]

anyone.......help....?

 

[cenderawasih]

OK....I've solve this problem......

I just need to add the me.setfocus command before requerying the form.........thanks to Tinus.......