By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,504 Members | 1,473 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,504 IT Pros & Developers. It's quick & easy.

Create and Update a table

P: 31
I have a form with a combobox on it. Based on user selection, I want to create a new table in the current database, define it and update it with values from a query, which again is based on user selection.

Can somebody please help me? Thanks a lot.
Jul 20 '07 #1
Share this Question
Share on Google+
6 Replies


P: 31
I have a form with a combobox on it. Based on user selection, I want to create a new table in the current database, define it and update it with values from a query, which again is based on user selection.

Can somebody please help me? Thanks a lot.

I got the answer:

I will modify query definition every time user selection changes. I can open the results of the query in a recordset and then modify the recordset as follows:

Sub test(strQuery As String, strField As String)
Dim db As Database
Dim rec As Recordset

Set db = CurrentDb()
Set rec = db.OpenRecordset(strQuery)

Do While Not rec.EOF
rec.Edit
rec(strField) = UCase$(rec(strField))
rec.update
rec.MoveNext
Loop
Sub

The above example is to make the data in cell of country column into Upper case.

Now, I have another question:

My task is to replace some of the values in the column.For example there are values like Drilling 1, drilling 2, drilling 3 and so on in the column and I want to make a string matching and update all of them to drilling. so what i exactly want to do is:

if rec(strField) like '*drilling*' then

rec(strField)="drilling"
End if

Can somebody help me do this? Thank you.
Jul 20 '07 #2

P: 65
Is it once in a lifetime change??? Or doe this have to happen multiple times.

In cas of the first, you can just open your table and use the search/replace function of access.
Jul 20 '07 #3

P: 31
Is it once in a lifetime change??? Or doe this have to happen multiple times.

In cas of the first, you can just open your table and use the search/replace function of access.
Query:

SELECT [Q].[QVN], [Q].[PID], [Q].[CT], [Q].G, Sum([Q].C) AS Cost
FROM qryP INNER JOIN [Q] ON qryP.[PID] = [Q].[PID]
GROUP BY [Q].[QVN], [Q].[PID], [Q].[CT], [Q].G HAVING ((([Q].[QVN]) Like '7.3'));

This is the query whose record set i want to edit. I want to edit values of [Q].[CT] in which there are fields like Drilling 1. Drilling 2, Drilling 3....so on, which i want to make just "Drilling" so that I can group them more generically. Now this query depends on user selection of [Q].[QVN] which is done through a combobox on a form.

So, I have a button below the combo box. The user selects the [Q].[QVN] from combobox and Query is generated. I want to open the recordset of the query replace the strings and then execute or requery the modified query.

Please advice.
Jul 20 '07 #4

P: 31
Query:

SELECT [Q].[QVN], [Q].[PID], [Q].[CT], [Q].G, Sum([Q].C) AS Cost
FROM qryP INNER JOIN [Q] ON qryP.[PID] = [Q].[PID]
GROUP BY [Q].[QVN], [Q].[PID], [Q].[CT], [Q].G HAVING ((([Q].[QVN]) Like '7.3'));

This is the query whose record set i want to edit. I want to edit values of [Q].[CT] in which there are fields like Drilling 1. Drilling 2, Drilling 3....so on, which i want to make just "Drilling" so that I can group them more generically. Now this query depends on user selection of [Q].[QVN] which is done through a combobox on a form.

So, I have a button below the combo box. The user selects the [Q].[QVN] from combobox and Query is generated. I want to open the recordset of the query replace the strings and then execute or requery the modified query.

Please advice.

I have multiple updations to be done on same field.
Jul 20 '07 #5

P: 65
It might be easier to put that query you made in to a temporary table so you can manipulate the data more freely. Manipulating a recorset is not that easy. I tried it last week and stepped away from it because I was going crazy on weird things. I made it easy for myself by making a temp table. Just clear it just before you have new data.

UPDATE tmpTable SET Q.CT = "Whatever you want"
Where Q.QVN = [Your input].[Dropdown]

This should be somwhat what you need to update your records. Only I think this is on a query and it should be on a table.
Jul 23 '07 #6

P: 31
It might be easier to put that query you made in to a temporary table so you can manipulate the data more freely. Manipulating a recorset is not that easy. I tried it last week and stepped away from it because I was going crazy on weird things. I made it easy for myself by making a temp table. Just clear it just before you have new data.

UPDATE tmpTable SET Q.CT = "Whatever you want"
Where Q.QVN = [Your input].[Dropdown]

This should be somwhat what you need to update your records. Only I think this is on a query and it should be on a table.
Thank you. I got the jist of your idea.
Jul 23 '07 #7

Post your reply

Sign in to post your reply or Sign up for a free account.