Hi all,

I am trying to design a form that has various text boxes and each of them displays one field name of the table (destinationtbl). So text box1 displays field name1 of destinationtbl.... text box2 display field name2 of destinationtbl and so on.

In front of each of these text boxes is a combo box which populates all the field names of a table temptbl.

How can i display the field names in the text boxes ?

Thanks

Check this post to get started. Then let me know what additional code you need: http://p2p.wrox.com/topic.asp?TOPIC_ID=65307

HTH

mmcdonal

Hi mmcdonal,

thanks for replying.

I do not want user to select the file neiher do i want to import a file.
I have 2 tables temptbl and destinationtbl. I want to transfer data from temptbl to destinationtbl and let the user chose which column to take the data to.
I have a form which has various text boxes and each text box has a column of table destinationtbl as the control source. In front of each text box is a combo box which populates all the field names of table temptbl. I also have a command button at the bottom of page which says "transfer data".
What i want is to let user choose a field name (of table temptbl) from combo box and when the "transfer data" button is clicked, the data from that column of temptbl will be transfered to the respective column (as specified on the text box) of table destinationtbl.

Can it be done by storing the value of choosen field name in a variable and matching it with the field names of destinationtbl and then appending the data ?
I cannot figure out how to do it.

Thanks for helping.

Yes, if you already have the combo boxes built with the field names, and you have a routine to make sure that your user does not enter duplicates, like

Source Table Destination Table
Column1 Column1
Column2 Column2
Column3 Column2
Column4 Column2

etc.

Then the way to do this would be:

Dim rsS As ADODB.Recordset
Dim rsD As ADODB.Recordset
Dim sSQLS As String
Dim sSQLD As String
Dim sCol1 As String
Dim sCol2 As String
Dim sCol3 As String
...

sCol1 = Me.Combo1
sCol2 = Me.Combo2
...
'You will want to check to make sure there are no duplicate selections, and that there is SOME selection (no nulls)for each column variable.

Then

'Open source recordset
Set rsS = New ADODB.Recordset
sSQLS = "SELECT * FROM SourceTable"
rsS.Open sSQLS, CurrentProject.Connection, adOpenDynamic, adLockReadOnly

'Open destination recordset
Set rsD = New ADODB.Recordset
sSQLD = "SELECT * FROM DestinationTable"
rsS.Open sSQLS, CurrentProject.Connection, adOpenDynamic, adLockBatchOptimistic

rsS.MoveFirst
Do Until rsS.EOF
   rsD.AddNew
   rsD(sCol1) = rsS("Column1Name")
   rsD(sCol2) = rsS("Column2Name")
   ...
rsS.MoveNext
Loop
rsD.UpdateBatch

rsS.Close
rsD.Close
...


That would do it. Did that help?




mmcdonal

To allow nulls for strings, do this:

If IsNull(Me.ComboString) Or Me.ComboString = "" Then
   sColumnStringVariable = ""
Else
   sColumnStringVariable = Me.ComboString
End If

To allow nulls for integers, do this:

If IsNull(Me.ComboInteger) Or Me.ComboInteger = 0 Then
   sColumnIntegerVariable = 0
Else
   sColumnIntegerVariable = Me.ComboInteger
End If

Did that help?



mmcdonal

Can you post your code? If you added what I wrote, then you will get errors. I just posted generic code, not that actual code.


mmcdonal

This code doesn't look like it would throw a Null error since it is an empty string, and not a null. Where are you getting the error? If here:

Do Until rsS.EOF
   rsD.AddNew
   rsD(sCol1) = rsS("Column1Name")
   rsD(sCol2) = rsS("Column2Name")
   ...
rsS.MoveNext
Loop
rsD.UpdateBatch


Then you probably have a table design issue (make fields not required) or you need to do this instead, which is not good, but will can be accounted for in subsequent reports, and moving data in:

If IsNull(Me.Combo0) Or Me.Combo0 = "" Then
        sCol1 = "Null"
    Else
        sCol1 = Me.Combo0
    End If

You may be having a problem with populating some fields but not others with this code, and those other fields are taking Nulls since you are not populating them, and they are throwing the errors. That would be an Allow Nulls = Yes issue.


mmcdonal

What is the data type for Column 1? It should be a string. Is it something else, like an integer or other number?


mmcdonal

It is string.

i have placed the code just before the loop..... is that ok ?

I would put it after the delcalrations, and before you waste time opening connections or recordsets. Can you do it in this order:

Declarations

Take Values

Open Connection(s)

Open Recordset(s)

Process Records

Close Recordset(s)

Close Connection(s)

Can you build this, then post ALL your code if it is not working?


mmcdonal