Klatuu, But that's not creating a temp table is it? My query looks like this:
SELECT q_Text.UCh_text, q_Primary_CT.SFY, q_Primary_CT.SAC_num,
q_Primary_CT.SAC_name, q_Primary_CT.UC_grp_name, q_Primary_CT.UC_name,
q_Text.UCh_num, q_Primary_CT.UCh_sort_num, q_Primary_CT.UR_name,
q_Primary_CT.bp_status, q_Primary_CT.AOB, q_Primary_CT.[Senate CC]
FROM q_Primary_CT INNER JOIN q_Text ON q_Primary_CT.UCh_num = q_Text.UCh_num;
which involves a crosstab that is never updatable. So, I create a module
(function) and put it on the f_PrimaryEntry form On Open event that looks
like this:
Option Compare Database
Function CreateTempTable()
Dim db As Database
Dim tdfTemp As TableDef
Dim fld1 As New Field
Dim fld2 As New Field
Dim fld3 As New Field
Dim fld4 As New Field
Dim fld5 As New Field
Dim fld6 As New Field
Dim fld7 As New Field
Dim fld8 As New Field
Dim fld9 As New Field
Dim fld10 As New Field
Dim fld11 As New Field
Set db = CurrentDb()
Set tdfTemp = db.CreateTableDef()
tdfTemp.Name = "tblTemp"
Set fld1 = tdfTemp.CreateField("UCh_text", dbMemo)
Set fld2 = tdfTemp.CreateField("SFY", dbLong)
Set fld3 = tdfTemp.CreateField("SAC_num", dbByte)
Set fld4 = tdfTemp.CreateField("SAC_name", dbText, 50)
Set fld5 = tdfTemp.CreateField("UC_grp_name", dbText, 100)
Set fld6 = tdfTemp.CreateField("UC_name", dbText, 125)
Set fld7 = tdfTemp.CreateField("UCh_num", dbLong)
Set fld8 = tdfTemp.CreateField("UCh_sort_num", dbLong)
Set fld9 = tdfTemp.CreateField("UR_name", dbText, 100)
Set fld10 = tdfTemp.CreateField("bp_status", dbText, 20)
With tdfTemp.Fields
.Append fld1
.Append fld2
.Append fld3
.Append fld4
.Append fld5
.Append fld6
.Append fld7
.Append fld8
.Append fld9
.Append fld10
End With
With db.TableDefs
.Append tdfTemp
.Refresh
End With
db.TableDefs.Refresh
Application.RefreshDatabaseWindow
Dim objectVar As New ObjectFrame
Dim tdf As New TableDef
End Function
But it is not populating my records. Any clue???
Thanks!
Klatuu said:
First, I am suprised you could not create a query that is updateable. I'm
not sure why that is, unless there are calculations in it or some other thing
that prevents updates. I can't remember all the rules on when a query is
updateable.
So, I would try using the queries you are using to display the data, but add
the button and code only do doing updates or adding new records. What I am
unsure of is whether you can change values in the controls on the form in
that situation.
:
Klatuu, Thanks for the information. So, what you're saying is that the form
will be unbound so I'll be using no record source but entering the code.
Where do I put the code on the f_PrimaryEntry form that you began with shown
below? Or is that wrong and I do use a make table query and bind it to my
form but I put in the code and command button as well?
Dim rstTableOne as Recordset
Dim rstTableTwo as Recordset
Set rstTableOne = CurrentDb.OpenRecordset("FirstTableName", dbOpenDynaset)
Set rstTableTwo = CurrentDb.OpenRecordset("SecondTableName", dbOpenDynaset)
Sorry to be a pain... I appreciate your help.
Tracy
:
hmmm...
The multiple users issue is a little trickier, because with only one table,
every user would be adding to the same table and that would create problems.
The other problem is that neither a query or a table of any kind will update
your tables if they are not bound to the form, so this is sort of like using
an unbound form. That is why I asked about your VBA experience. This is the
only way you are going to be able to do what you want.
I don't have time to write all the code for you, but here are a few pointers.
First you will have to open the tables you need to update as dbOpenDynasets
Dim rstTableOne as Recordset
Dim rstTableTwo as Recordset
Set rstTableOne = CurrentDb.OpenRecordset("FirstTableName", dbOpenDynaset)
Set rstTableTwo = CurrentDb.OpenRecordset("SecondTableName", dbOpenDynaset)
Now, once you have all the data in, you will need a command button to
actually do the updates. You would put the code to do the updates in the
Click event of the command button. This code doesn't do any error handling
or validation. You will have to add that.
First, you need to determine whether it is a new record:
strFind = "[FIELD_NAME_TO_MATCH_1] = '" Me.txtFirstField & "' AND
[FIELD_NAME_TO_MATCH_2] = '" & Me.txtSecondField & "'"
With rstTableOne
.FindFirst strFind
If .NoMatch Then
.AddNew
Else
.Edit
End If
!FieldOne = Me.txtSomeField
!FieldTwo = Me.txtAnotherField
.Update
End With
And do the other table the same way.
Good luck! This is a great learning experience for you
:
I created a select query using a crosstab and another necessary query but it
still recognized it as a crosstab and wouldn't let me update my form so I
created a make table from that select query and named it f_PrimaryEntry.
However, this won't work for multiple users and updating the tables
dynamically. So, from what I read, I need a temp table in DAO but my
experience in VB is very limited and I seem to be going in circles with what
I'm attempting. Thanks!!!
:
So, the crosstab query is the record source for the form, but you need to
update a table based on changes made in the form? If so, this will take a
little work. Let me know if my understanding is correct, and give me an idea
of your experience level with VBA, and I can give you a solution or two
:
Yes, but because it's fed from the crosstab, it won't let me edit the form so
I made a make table. The only thing is, the make table needs to be a temp
table because I could have multiple users at the same time. So, I created a
database with the f_Temp file. I don't understand though how I get that
temporary file back into my current database but I'm thinking that if I do a
Temp table in DAO then that will solve my problem. From what I understand,
it can be created to update all the other relating tables but I can't figure
out how to put it all together. There are only two fields on the
f_PrimaryEntry form that are updatable - UCh_sort_num and UCh_amount. I am
so desperate to get this completed and I really appreciate your expertise.
Tracy
:
Have you tried using a select query as your record source for f_PrimaryEntry?
:
I have a switchboard that opens my Home form that when clicked upon in two
errors has code to open my f_PrimaryEntryScreen which has two subforms -
f_BudgetUnit and f_PrimaryEntry. When I click on a record in f_BudgetUnit it
opens up in f_PrimaryEntry the appropriate records. f_PrimaryEntry's record
source is a table because I can't use a crosstab query to update on my form.
With that said, (perhaps more info than necessary), because I will have
multiple users, possibly at the same time, I need my table to be a temp table
and to automatically update my other tables. I believe I need to do I Make
table in DAO. Can you give me some direction? I'm not even sure where I
begin to write the code in the f_PrimaryEntry form. Thanks a ton!
