Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

Updatable Cross tab form

Posted on 2011-04-20
15
Medium Priority
?
447 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.  

0
Comment
Question by:lulubell-b
  • 6
  • 5
  • 3
  • +1
15 Comments
 
LVL 22

Expert Comment

by:Nico Bontenbal
ID: 35438383
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.
0
 
LVL 40

Expert Comment

by:als315
ID: 35438413
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
0
 

Author Comment

by:lulubell-b
ID: 35440309
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
0
Get free NFR key for Veeam Availability Suite 9.5

Veeam is happy to provide a free NFR license (1 year, 2 sockets) to all certified IT Pros. The license allows for the non-production use of Veeam Availability Suite v9.5 in your home lab, without any feature limitations. It works for both VMware and Hyper-V environments

 
LVL 22

Expert Comment

by:Nico Bontenbal
ID: 35440353
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.
0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 35440365
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




0
 

Author Comment

by:lulubell-b
ID: 35440431
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.
0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 35440491
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
0
 

Author Comment

by:lulubell-b
ID: 35440602
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;
0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 35440723

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?
0
 

Author Comment

by:lulubell-b
ID: 35440903
PKID
PRIMARY KEY FIELD is TEXT
0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 35440916
private sub grp_dd_afterupdate()

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


end sub
0
 

Author Comment

by:lulubell-b
ID: 35440945
Are the quotes like this '" or ""
0
 
LVL 120

Accepted Solution

by:
Rey Obrero (Capricorn1) earned 2000 total points
ID: 35441043
e x p a n d e d   v i e w


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

Expert Comment

by:Nico Bontenbal
ID: 35441053
<<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.
0
 

Author Closing Comment

by:lulubell-b
ID: 35441531
Thank you
0

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Code that checks the QuickBooks schema table for non-updateable fields and then disables those controls on a form so users don't try to update them.
Microsoft Access has a limit of 255 columns in a single table; SQL Server allows tables with over 255 columns, but reading that data is not necessarily simple.  The final solution for this task involved creating a custom text parser and then reading…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…

580 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question