We help IT Professionals succeed at work.

Check out our new AWS podcast with Certified Expert, Phil Phillips! Listen to "How to Execute a Seamless AWS Migration" on EE or on your favorite podcast platform. Listen Now

x

Updatable Cross tab form

lulubell-b
lulubell-b asked
on
Medium Priority
473 Views
Last Modified: 2012-06-27
Hello,

I created a form that is retrieving data using a cross tab query. I also have drop down columns with in the form. Since, my cross tab is not updatable how should I go about coding this?

I was thinking of an On change  () event

Each row has the primary key is displayed. So, when one of my drop down is selected and changed then update the appropriate table where the primary key equals. I need help writing this. My background is PL/SQL and some C#.Net not VBA.

I'm unsure how to grab the primary key data and store it then grab the selected item that is being updated and modify the source table.  

Comment
Watch Question

CERTIFIED EXPERT

Commented:
You could create a temporary table that is filled with the data from the crosstab when the form opens. When the form closes you run an update query that stores the updated values in the temp table back into the database.
You could also use the afterupdate event (of a control, or the entire record) to store the data in the database just after the user changes it.
CERTIFIED EXPERT

Commented:
If you can upload your form with sample data, we will be able to give detailed answer.
Now only general recommendations:
Create query, which will update necessary record, and run in from VBA

Author

Commented:
To answer the first question, I don't want to use temporary tables to update this. I want it to flow from the form to the appropriate table.

Data....

GRP      PRIMARY KEY FIELD        TYP
ABC      11002                        C999
HHI      50003                        C123
DEF      61111                        C55


The form is pretty simple, a drop down list for the GRP column and TYP column. The PRIMARY KEY FIELD is static.

At the current moment I have a crosstab query that populates the form. When a new drop down value is selected I want to update the approriate association table. For example, when I change KEY FIELD 11002 GRP to DEF instead of ABC then I want to update the corresponding table based on the PRIMARY KEY FIELD (11002)

Thank you
CERTIFIED EXPERT

Commented:
I don't understand why you are using a crosstab query. The data in the example above, is that the data in the table or the result from the crosstab. Could you upload a sample of your application with only this table, query and form, or at least post a screenshot of the form.
CERTIFIED EXPERT
Top Expert 2016

Commented:
lulubell-b,

<then I want to update the corresponding table based on the PRIMARY KEY FIELD (11002)>

update what field with what value?

something like this, perhaps in the afterupdate event of the drop down box

private sub combo0_afterupdate()

currentdb.execute "update tablex set fieldx=somevalue where PKfield=" & me.combo0

end sub




Author

Commented:
The data is from the cross tab query. I'm using a cross tab because of the way I'm displaying the data on the form.

Update teh GRP field to "XXX" where PRIMARY KEY = 11002.

What do I set the fieldx=somevalue to?

the name of the drop down? or me.

Not familiar with how to pass variables from from form to code.
CERTIFIED EXPERT
Top Expert 2016

Commented:
ok., give us the
name of table
name of field
name of the dropdown
rowsource of the dropdown

better upload a copy of your db. upload a .MDB version

Update NameOfTable set NameofField="XXX" where PrimaryKeyFieldName=11002

Author

Commented:
I'm not uploading a copy of my db.

Just use generic object names

TABLE: A
FIELD: GRP
DROPDWN: GRP_DD
ROWSOURCE: SELECT tblGRP.GRP FROM tblGRP;
CERTIFIED EXPERT
Top Expert 2016

Commented:

private sub grp_dd_afterupdate()

currentdb.execute "update A set GRP='" & me.grp_dd & "' where PKfieldName=11002"


end sub


what is the name of the control in the form that display the  " PRIMARY KEY FIELD"

is  PRIMARY KEY FIELD  Text or NUmber data type?

Author

Commented:
PKID
PRIMARY KEY FIELD is TEXT
CERTIFIED EXPERT
Top Expert 2016

Commented:
private sub grp_dd_afterupdate()

currentdb.execute "update A set GRP='" & me.grp_dd & "' where PKID='" & me.pkid &"'"


end sub

Author

Commented:
Are the quotes like this '" or ""
CERTIFIED EXPERT
Top Expert 2016
Commented:
Unlock this solution and get a sample of our free trial.
(No credit card required)
UNLOCK SOLUTION
CERTIFIED EXPERT

Commented:
<<I'm not uploading a copy of my db. >>
We're not after your data or code. Just trying to help. Do don't need to upload your actual database and/or data. Just create a small db with a table, a crosstab and a form to clarify you problem to us. Check Capricorn's profile. If he asks for more info you can assume your question is not clear enough.

Author

Commented:
Thank you
Unlock the solution to this question.
Thanks for using Experts Exchange.

Please provide your email to receive a sample view!

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.