Wrox Programmer Forums
| Search | Today's Posts | Mark Forums Read
Access Discussion of Microsoft Access database design and programming. See also the forums for Access ASP and Access VBA.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Access section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
 
Old October 1st, 2007, 12:29 PM
Authorized User
 
Join Date: Sep 2007
Location: , , USA.
Posts: 35
Thanks: 0
Thanked 0 Times in 0 Posts
Default now error 3265

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
 
Old October 1st, 2007, 02:24 PM
Friend of Wrox
Points: 9,611, Level: 42
Points: 9,611, Level: 42 Points: 9,611, Level: 42 Points: 9,611, Level: 42
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Mar 2004
Location: Washington, DC, USA.
Posts: 3,069
Thanks: 0
Thanked 10 Times in 10 Posts
Default

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
 
Old October 1st, 2007, 02:51 PM
Authorized User
 
Join Date: Sep 2007
Location: , , USA.
Posts: 35
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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.

 
Old October 2nd, 2007, 06:50 AM
Friend of Wrox
Points: 9,611, Level: 42
Points: 9,611, Level: 42 Points: 9,611, Level: 42 Points: 9,611, Level: 42
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Mar 2004
Location: Washington, DC, USA.
Posts: 3,069
Thanks: 0
Thanked 10 Times in 10 Posts
Default

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
 
Old October 3rd, 2007, 07:04 AM
Friend of Wrox
Points: 9,611, Level: 42
Points: 9,611, Level: 42 Points: 9,611, Level: 42 Points: 9,611, Level: 42
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Mar 2004
Location: Washington, DC, USA.
Posts: 3,069
Thanks: 0
Thanked 10 Times in 10 Posts
Default

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
 
Old October 3rd, 2007, 08:29 AM
Friend of Wrox
Points: 9,611, Level: 42
Points: 9,611, Level: 42 Points: 9,611, Level: 42 Points: 9,611, Level: 42
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Mar 2004
Location: Washington, DC, USA.
Posts: 3,069
Thanks: 0
Thanked 10 Times in 10 Posts
Default

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
 
Old October 3rd, 2007, 10:28 AM
Friend of Wrox
Points: 9,611, Level: 42
Points: 9,611, Level: 42 Points: 9,611, Level: 42 Points: 9,611, Level: 42
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Mar 2004
Location: Washington, DC, USA.
Posts: 3,069
Thanks: 0
Thanked 10 Times in 10 Posts
Default

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
 
Old October 3rd, 2007, 01:36 PM
Friend of Wrox
Points: 9,611, Level: 42
Points: 9,611, Level: 42 Points: 9,611, Level: 42 Points: 9,611, Level: 42
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Mar 2004
Location: Washington, DC, USA.
Posts: 3,069
Thanks: 0
Thanked 10 Times in 10 Posts
Default

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


mmcdonal
 
Old October 3rd, 2007, 02:08 PM
Authorized User
 
Join Date: Sep 2007
Location: , , USA.
Posts: 35
Thanks: 0
Thanked 0 Times in 0 Posts
Default

It is string.

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


 
Old October 3rd, 2007, 02:13 PM
Friend of Wrox
Points: 9,611, Level: 42
Points: 9,611, Level: 42 Points: 9,611, Level: 42 Points: 9,611, Level: 42
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Mar 2004
Location: Washington, DC, USA.
Posts: 3,069
Thanks: 0
Thanked 10 Times in 10 Posts
Default

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




Similar Threads
Thread Thread Starter Forum Replies Last Post
Insert Query Error & Run-Time Error 3022 DavidWE Access 1 July 31st, 2008 11:17 AM
Ch 4: Parse error: syntax error, unexpected T_SL hanizar77 BOOK: Beginning PHP5, Apache, and MySQL Web Development ISBN: 978-0-7645-7966-0 0 June 23rd, 2008 09:17 PM
[Resolved] Error calling a sp - parameter error snufse .NET Framework 2.0 2 February 12th, 2008 04:46 PM
Parse error: syntax error, unexpected T_STRING ginost7 Beginning PHP 1 November 9th, 2007 02:51 AM
Phile Page error, visual studio error reps BOOK: ASP.NET Website Programming Problem-Design-Solution 0 September 27th, 2003 10:11 AM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.