Create and Update a table

[Flo100]

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.

[Flo100]

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.OpenRecordse t(strQuery)

Do While Not rec.EOF
rec.Edit
rec(strField) = UCase$(rec(strF ield))
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.

[Twanne]

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.

[Flo100]

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.

[Flo100]

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.

[Twanne]

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.

[Flo100]

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.