Visual Basic :: Adding A New Field.

Adding Two Field's Data Ino One Field
hello

The following SQL command adds two fields data into one field.

tbl1 structure:
_____________________________
field1 field2

a1 b1
a2 b2
a3 b3
a4 b4
____________________________

Select field1,field2 as fieldNew from tbl1;

the result is :

___________________________
fieldNew

a1b1
a2b2
a3b3
a4b4

__________________________

But I want to get:

_________________________

fieldNew

a1
a2
a3
a4
b1
b2
b3
b4

________________________

Please anyone help me to solve this problem.

Thank you all.

Bye...

Adding Two Field's Data Into One Field Using SQL.
Hello...

Can anyone tell me how can I Get two different field's data into one (newly named) Field using SQL?or VB?

Adding A Field On The Fly
Is there a way to add a field to a database table through code in ADO?
thanks very much, ted

Adding A Field
I am trying to add a field to a DB, with SQL. Is this possible? Where should I look?

Adding A Hyperlink To A Field Using VBA
Hi everyone. I'm trying to link to a file with a hyperlink. It's the only way I can think to do this. we currently get faxes sent to our desktop. I need to be able to double click my attachment field, pick my file, have it asign a unique name to the file, move it to my attachment folder, and then insert a hyperlink in the text box. I've managed to do everything except get the hyperlink to work. It puts the full path to my new file in the text box, but it doesn't do anything when I click on it. It looks like a hyperlink, but doens't act like one. Here is the code I have so far:

Code:
Private Sub Attachment_DblClick(Cancel As Integer)

Dim fs As Object
Dim dlgOpen As FileDialog
Dim vrtSelectedItem As Variant
Dim myDir As String
Dim OldName, NewName
Dim myString As String
Dim SourceFile, DestinationFile
Dim strFileName As String

myString = Format(Now(), "yyyymmddhms")

Set dlgOpen = Application.FileDialog(msoFileDialogFilePicker)
Set fs = CreateObject("Scripting.FileSystemObject")

With dlgOpen
.AllowMultiSelect = False
.Show

For Each vrtSelectedItem In .SelectedItems
myDir = vrtSelectedItem
Next vrtSelectedItem

End With

strFileName = Right(myDir, Len(myDir) - InStrRev(myDir, "", -1))

myEnd = Right(myDir, 4)

fs.CopyFile myDir, CurrentProject.Path & "Attachments"

OldName = CurrentProject.Path & "Attachments" & strFileName: NewName = CurrentProject.Path & "Attachments" & myString & myEnd

Name OldName As NewName

Me.Attachment.Value = NewName

End Sub
I've set the field type to Hyperlink. Can anyone tell me either a better way to link to a document, or how to use the value in the cell as a hyperlink?

Thanks for any help!

Dave

P.S. Forgot to mention, I'm using Access 2002 (XP)

Adding A Field To My Table
i am using this to make a table and fields in my database

Set db = OpenDatabase("C:WINDOWSSYSTEMDailyacivity.mdb")
Set tb = New TableDef
Set f = New Field
f.Name = "Date"
f.Type = dbDate
f.Name = "Shift"
f.Type = dbText
tb.Name = intpress
tb.Fields.Append f
db.TableDefs.Append tb

it makes the table but only one of the fields the shift field how can i add more fields?

Adding A New Field To A Table
Is it possible to add a new field to an existing table throught a SQL statement?
I've searched the forum but I did not find anything, can anyone help me?

Adding Field With ADOX
I'm trying to add a field to a table using ADOX. Adding the field is easy but I can't figure out how to set its default value. What should I add to the following code?

Dim oCat As ADOX.Catalog
Dim oTable As ADOX.Table
Dim oColumn As ADOX.Column

Set oCat = New ADOX.Catalog

oCat.ActiveConnection = _
"Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source= MyDatabase.mdb;"
Set oTable = oCat.Tables("MyTable")
Set oColumn = New ADOX.Column
With oColumn
.Name = "NewField"
.Type = adUnsignedTinyInt
End With

oTable.Columns.Append oColumn

Set oColumn = Nothing
Set oTable = Nothing
Set oCat = Nothing

Thanks!

Adding Field To Db Programmatically
I need to be able to programatically add a field of type text with a length of 70 to a database in ADO. If I can do this through code it will save me a very long and unnecessary trip.

thanks, Ted

Adding A New 'field' To The Database
I can't seem to figure out a easy way to add a field to a database.. This is what I'm trying.

Private Sub AddDetailDBFields(sFileName As String)
Dim adoRS_DetailFields As ADODB.Recordset

'Assume Connection Open to adoConn_SQLDB_Local

'Open Recordset
Set adoRS_DetailFields = New ADODB.Recordset
adoRS_DetailFields.Open "Detail", adoConn_SQLDB_Local

adoRS_DetailFields.Fields.Append "New Field Name", adDouble

adoRS_DetailFields.Close
End Sub

All I'm trying to do is add a new Field to a Database Table.
Thanks for the help.

Adding New Field In To Access
Hi All

We have a VB program which was written originally in VB5 and then we upgraded to VB6. Our original database was designed a few years ago in Access version 2. It only contains 5 fields. We use a data control to link to our Access file.

My question is:

Through our program is it possible to extend this to 6 fields by using a command code to insert another field which can also be incorporated in the program being already used?

The reason I ask this is that among the people that use the program, some have Access on and some do not.

Any help would be appreciated.

Thanks

Steve

Problem Adding DAO Field
i'm trying to append new field on existing database table but my code not working here's my current code using DAO:

Code:
Sub AddField()
Dim tblObjectTracking As TableDef
Dim AddField As Field
Dim X As Integer
Dim Fld As Field
On Error Resume Next
Set db = OpenDatabase("logfile.mdb", False, False, ";pwd=pass")
Set tblObjectTracking = db.TableDefs("log")
Set Fld = tblObjectTracking.CreateField("OT", dbText, 250)
With Fld
.AllowZeroLength = True
End With
With tblObjectTracking.Fields
.Append Fld
.Refresh
End With
With tblObjectTracking.Fields
.Append Fld
.Refresh
End With
db.TableDefs.Append tblObjectTracking
db.Close: Set db = Nothing
Set tblObjectTracking = Nothing
End Sub
anyone can help... thanksss
Edit by Moderator:
Posting a thread in a discussion forum is a pretty good indication that you require help, so no need to put that in your thread title.

Adding Data To A New Field
Hi,

I have used the following code to add a new field to a database. Is there a way to stop this field having a Null value but still keep it empty?

Code:

strSQL = "SELECT * FROM cells ORDER BY cell, Proof;"
Set db = OpenDatabase(lblDatabase.Caption, False, False)

Set tbl = db.TableDefs("cells")

' Create and append new Field objects
tbl.Fields.Append tbl.CreateField("Extra", dbText, 20)

Adding Together One Field In All Records.
In my database their is one field that has the revenue for one item how would i add together each of them to get a total revenue?

I also want to be able to delete one field in different records e.g. delete the revenue for each item how would i go about doing that?

Thanks for your help

Adding New Field To Table Through SQL
I have been asked to write a piece of SQL to add a new field to an existing table (SQL Server).

I haven't got a clue how to do it!

Can anyone help me?

Adding 2 Field Primary Key Using DAO
One last question...I want to define 2 fields as the primary key in a Jet database using DAO. I understand how to create an index on a single field using createindex but I am having problems setting 2 fields as a primary key. Any help would be greatly appreciated. Thanks in advance!!

Adding Field To Table On Import
How can I add a field to a table as I loop through and import a months worth of Excel Info. The field will contain the date (120103) using a string to identify the data.
the code is below. the command15_click() works fine. Its the addfield2() code where I get an error message. RunTIme error "3421" Data type conversion. any ideas would be helpful
for example: I need the first field to be:
120103 data...data..data
120203 data..data...data

Right now I only have data.

Code:
Private Sub command15_click()
Dim strname As String
Dim stryear As String
Dim itimer As String
Dim filestr As String
itimer = 31
strname = "I:Socc
eportsDailyLog_"
stryear = Combo23
filestr = Combo18 & Combo23
Call CLEAR_LOAD

Do While (itimer > 0)
strpath = strname & Me.Combo18 & Format$(itimer, "00") & stryear
DoCmd.TransferSpreadsheet acImport, 8, filestr, strpath, False, "C55:AA55"

Call addfield2

itimer = itimer - 1
Loop
MsgBox "FINISHED"

Sub addfield2()
filestr = Combo18 & Combo23
Set dbs = CurrentDb
Set tdf = dbs.TableDefs!(filestr)
' Create new field in table.
Set fld = tdf.CreateField(filestr, Number, 11)
fld.OrdinalPosition = 1
With TDef
.Fields.Append .CreateField("TextField9", dbText)
.Fields.Append .CreateField("IntegerField10", dbInteger)

End With
Set dbs = Nothing
End Sub

Adding One Or More Lines In A Memo Field
is there a fuction I can use to add lines in a memo field using VBA??

Adding A Field To An Access Table
Greetings,
On a particular user action, I would like to add a new field to an Access table. Is there a dynamic way to do so. ( kinda like when we are creating a table at Design time...)
Thanx

Adding A Field To The Begining Of A Recordset
Is there anyway of adding a field to the beginning of a ADODB recordset which is already populated? I would rather not changed the stored procedure which creates the recordset or have to create a new stored procedure. I don't think rs.Fields.Append can do this, can it?

Cheers

G

Getting Field Names And Adding To Lstbox
I am using the following code to try and get the name of the columns in my access database and am having no luck. Basically I want to add the names of all the columns to a listbox in the order they appear in the database:

Code:
Set ElementConn = New ADODB.Connection
Set ElementRecSet = New ADODB.Recordset

ElementConn.CursorLocation = adUseClient
ElementConn.ConnectionString = "Driver={Microsoft Access Driver (*.mdb)};Dbq=" & App.Path & "Periodic Table.mdb;"
ElementConn.Open

Set ElementRecSet = ElementConn.Execute("SELECT * FROM Table2 ORDER BY AtomicNumber")

Dim i As Long

For i = 1 To ElementRecSet.Fields.Count
lstline1.AddItem ElementRecSet.Fields(i).Name
lstline2.AddItem ElementRecSet.Fields.Item(i).Name
lstline3.AddItem ElementRecSet.Fields.Item(i).Name
Next i

ElementConn.Close
I get an error at the red line (Runtime error 3265) "Item could not be found in the collection corresponding to the requested name or ordinal"

Any ideas?

Thanx

Adding Field To Index (on-the-fly) In Access Using DAO
So, I'm trying to change / "upgrade" the structure of an access database using (for reasons that are too long to go into now) DAO

I've stored all the "relevant" info in a custom file structure, and I'm then comparing that with the structure of the DB.

Adding New tables, fields, indexes works fine...

But when I try to add a newly created field to the Primary Index using something like this

Code:
Set td = db.Tabledefs(TableName)
Set id = td.indexes(IndexName)
id.Fields.Append td.Fields(FieldName)
I get Error 3367 - Can't append. An object with that name already exists in the collection.
But it doesn't / can't already exist because I only just created the field.. I've checked the Fields collection of the index and the field I'm trying to add definitely isn't there.

So I tried it a different way....
First Delete the index...
Then Recreate the entire index..
This gives me exactly the same error when I try to add the first field (which is not the new field) to the index... but at that point the id.fields.count = 0, I just created the index object, so it's empty....

So I'm guessing that the error is occuring for a reason other than the VB Help "stated" reason.... does anyone have any idea what else could cause this error?

Adding Nullable Field To Recordset?
hi all,

i have a recordset that I create in code. trouble is one of my fields needs to be able to be blank, but i get a

"canoot set non nullable field to null" error.

here's my recordset creating code:

sfsRS.ActiveConnection = Nothing

With sfsRS.Fields
.Append "Item_No", adBigInt
.Append "Description", adBSTR
.Append "Seq", adInteger
.Append "Cases Per Hour", adInteger
.Append "Qty", adDouble
.Append "Load (Hours)", adDouble
.Append "SHIP_DATE", adBSTR
.Append "SHIP_TIME", adBSTR
.Append "Comments", adBSTR
End With

the field i need to have allowed nulls is "Seq".

Adding Field Names To A Table
I have the following code which adds fields to a table. The problem is that some of the fields will end up having spaces in the names. This creates a problem because then vb assumes that the second word in the name is actually the variable type definition.
How do i get around this?

myConnect.Execute "ALTER TABLE Mark_Test ADD COLUMN " & SelMag(l) & " text;"

Adding A Calculated Field To A Datagrid...
Hi,

i asked this quastion once and no one answered me...
I have a function Age() on my module, it gets a date and calculates the age of this person with this date of birth...
I have a players table and when i put it in a datagrid i want to add another column which will use the players' date of birth and calculate this persons age, using the function on my module...

how can i do it?

please help... thanks a lot.

Adding Data To Database Field
Hi there,

I'm not sure at all how to do this so assistance will be much appreciated! I need to add a quantity amount (txtQuantity) to the field "Quantity" in my "CurrentStock" database. But it has to add to the value that is already in that field. Also if the txtProduct is "Laptop" then this amount needs to be added to the record 'Laptop' quantity. (i hope this makes sense!?) Heres my attempted code:

VBCODE Code:
Private Sub cmdUpdate_Click()
'When button is clicked, it will check input from check box and msgbox then update'
If Check1.Value = vbChecked Then
OrderMsgBox = MsgBox("Please confirm that this order has been completed", vbExclamation + vbYesNo)
'If box is ticked and yes is clicked it will update the "completed" field'
If (Check1.Value = vbChecked) And (OrderMsgBox = vbYes) Then
UpdateCompleted
Unload Me
UpdateStock1.Show
End If
End If
End Sub

Private Function UpdateCompleted()
'With condition to edit and update the completed field in the order table'
With datOrder.Recordset
.Edit
.Fields("Completed") = Check1.Value

.Update

[color="Red"]If txtProduct.Text = "Laptop" Then
Add txtQuantity.Text[/color] (stuck here!)

End Function

Adding Database Field Attributes
From VB I have loaded up a .mdb file
I was just wondering how can I edit the field attributes

e.g.
The Description attribute
the Validation rule
default value etc..

Also, is there a good place to look to show how I can import values from an excel spreadsheet into my VB program!

Thanks

Adding A Combo Box To One Of The Field Of A DBgrid
frnds
Let me know is thre any method to add a combo box to one of the field of a
dbgrid??
thanks for any help

Adding Text To Listview Field?
Hi all.

I have a program that has about 7 text fields and I have one listview. I have an add button for adding a new contact, like an address book program, that will add all text fields respectively to the listview fields. Instead of using a database this time I'm using a dat file to store the listview saved information from the textfields. So far I have this:

Code:
'I know this doesn't look right
Private Sub cmdAddNew_Click()
Dim xItem As ListView
Dim intList As Integer

lstContacts.ListItems.Clear

For intList = 1 To lstContacts.ListItems(intList).ListSubItems
  If lstContacts.ListItems(intList).ListSubItems > 0 Then
    With lstContacts.ListItems(intList).ListSubItems
    .Add intList, , txtID.Text
    .Add intList, , txtFirstName.Text
    End With
  End If
Next intList

End Sub
------------------------------------------------------------------------------
Private Sub cmdListAll_Click()
Dim xItem As ListItem

lstContacts.ListItems.Clear

Open App.Path & "Contacts.dat" For Binary As #1
Get #1, , cUser
Close #1

With cUser 'cUser is in a module as a public type
  txtID = .Contact_ID
  txtFirstName = .Contact_FirstName
  txtLastName = .Contact_LastName
  txtAge = .Contact_Age
  txtEmail = .Contact_Email
  txtPhoneNumber = .Contact_Telephone
  txtAddress = .Contact_Address
  txtLocation = .Contact_Location
  txtComments = .Contact_Comments
End With

Set xItem = lstContacts.ListItems.Add(, "ID", txtID, , 1)
xItem.SubItems(1) = txtFirstName
xItem.SubItems(2) = txtLastName
xItem.SubItems(3) = txtAge
xItem.SubItems(4) = txtEmail
xItem.SubItems(5) = txtPhoneNumber
xItem.SubItems(6) = txtAddress
xItem.SubItems(7) = txtLocation
xItem.SubItems(8) = txtComments

End Sub

I did get somethiing back from it but it looked like the "ID" text, not the ID number in the id section, and in another case 0's in the text fields that hold strings, lol. At least my machine is still running.

If anyone would know how close or far off I may be that would be great. Thanks for all your time.

RJ

Adding Record With Autonumber Field In SQL
Im adding new record in my SQL table with an autonumber field, but when i use the rs.update method it says that recid is null. Recid is the autonumber field in my table. Isnt it should be automatically generated and you dont need to put values in it? What is the reason for that error?

Thanks

Adding Hidden Field To Combo Box.
Is it possible (I thought it was) to add a hidden field to a combo box, i.e I want to have Text in the combo box that the user sees etc..but when it selects something then I want to use internally in code the ID that is attached to the text since my Design of my database is:

ID: Some number
Name: Name of Item

so is this possible? if so how? i suspect i need something different from cboBox.AddItem since the Item is just a string text field (what i would use for name) any help on how to add this hidden field of ID's and retrieve them later would be greatly appreciated thanks!

Adding A Yes/No Field To An Existing Table Using DAO.
Hi,
Do anyone know how to add a Yes/No field to an existing table using DAO?
(MS Access)

Adding Images To An Imagelist From A Db Field?
Does anyone know how I can add an image to a listbox from an access field.

I.e.

imaglist1.imagelist.add ID,Key,rsRecordset.fields("Picture").value

I have tried to set the picture property of an image control to the field as well but I get the same problems.

Nick

Adding Quotes Into String Field
This is probably so easy, it is embarassing. In VB I have a variable containing sort fields. It is ok for ascending order, but if the user wants descending I am having trouble adding the DESC to the end of it.
RS.Sort = Sort_Parameters & " DESC"

Sort_parameters(is selected by the user) might be ID, TagNum
so I want this .. "ID,TagNum DESC" but I don't know how to group these together. Thanks guys.

Adding Field Value To Outlook Subject Line
oMailItem.Subject = "Endorsement Request"

I HAVE THE ABOVE BEING ADDED TO AN EMAIL OF A REPORT IN OUTLOOK FROM ACCESS.
I WOULD LIKE TO BE ABLE TO ADDED THE VALUE OF A FIELD IN THE FORM THAT THE REPORT IS LAUNCHED FROM TO THE END OF THAT SENTENCE. HOW DO I ACCOMPLISH THAT. I AM VERY UNCERTAIN OF THE SYNTAX. THE FIELD VALUE IS ALSO IN THE QUERY THAT THE REPORT IS BUILT ON SO I AM SURE ITS JUST A MATTER OF ADDING IN THE RIGHT SYNTAX.

Please Help...on Adding Field To A Password Protected Database
Could anybody help me with adding new field to a database that is in Access 2002 format and also has a password....

somehow nothing seems to be working at my end...nor ADO or DAO...

Help of any kind would be greatly welcomed

VB6 Adding Data To Access Table Field
Ok, I have a feeling this is going to be a tricky one. I'm going to try to make this as simple as possible.
I'm making a VB6 Application that stores, retrieves, and accesses data from an Access 03 Table

I've got most of the code down, but this one problem. I'm making this portion of the program where the user can enter a Tool for repair into the database. I have a table where these tools are stored called ToolDataBaseTable. Within this Table, there are numerous Fields. Many of these fields are what we call Dispositions. The Disposition of a tool, is where it's at, ie. Machine Shop, Welding, so forth. Well, the user has to capability of Adding New Dispositions to to accomidate their own situations. The Table has to keep track of how many days the tool is in each disposition. So, say we have the following fields(columns) in the Table

ToolID CurrentDisposition MachineShopDays
PWA11 MachineShop 1

WeldingDays(also a column)
2

Ok, so the user adds a new disposition, the code is already prewritten so, I don't have the code to count the days in that disposition, so I have to substitute this.

This is how I would normally do it
MyData is the connection
rscmdViewToolDataBaseTable is the Recordset for the table. Machine shop is the field

MyData.rscmdViewToolDataBaseTable!MachineShopDays = strTotalFlowDays

I mean, if I had a bunch of preset Dispositions and the user wouldn't add one, I could just use a bunch of If Thens to Determine which Disposition needs to be updated and then use the appropriate code
but where I have MachineShopDays, I need there to be a variable there that I've already created. That has the Appropriate Disposition in it. I've tried something to the extent as this:

MyData.rscmdViewToolDataBaseTable! & strDisposition = strFlowDays

where strDisposition is the appropriate Field to add the days to. I know this is probably sounding wierd, but help would be greatly appreciated

Any input ? This one has got me stumped....

Adding A Vb Text Field To A MS Access Database
I have a vb program where I have a text field that has about 20 lines of text and I would like to store this text in a field in an access database. This is my first time working with vb and access together. Any help would be appreciated. Thanks.

Adding Data To A Newly Created Field
Hi,

I have used the following code to add a new field to a database. Is there a way to stop this field having a Null value but still keep it empty?

Code:
strSQL = "SELECT * FROM cells ORDER BY cell, Proof;"
Set db = OpenDatabase(lblDatabase.Caption, False, False)

Set tbl = db.TableDefs("cells")

' Create and append new Field objects
tbl.Fields.Append tbl.CreateField("Extra", dbText, 20)

Adding Field To Msaccess Table At Run Time Through VB
hello,

i have created a table which has x number of fields. However at run time i have a form which accepts additional fields which need to be added to the table structure. Is this possible? If yes, then how..or if no..then is there any other alternative way how i can i do it?

also, using crystal reports i have generated a reprt..now in this report i want to display two different sections of data. I mean i will be dispaying data from two different unrealted queries and tables. Is this possible?

gayatri

Adding Field Column In Visual Basic
hi i just need help on how to add in visual basic like field
column where i can populate with result of my query.. where you
can add additional field column to have more detail result..
does visual basic have that? example of field column like in
windows media player where you can add to view the album of the
song, artist, and type of file..

i hope visual basic have that function.. pls help me.. thanks

Adding Multiple Serial Number Into One Field
Hello all,

Is there any way to add multiple serial numbers into one database field and have it listed something like this

Example:
Returned_Equipment (This would be my field)

10010
10070
17010
17035
on and on

Is there another way besides using something like Return1, Return2, Return3 and so on?

I am using Microsoft Access 2000 with Visual Basic 6 Pro.
Still trying to figure all this database stuff out.

Thanks in Advance,

Mike

Mike Haas

Adding To A Text Field/label In A For Loop
i'm thinking along the lines of the
command in c++ here....

how would i make it so that in a for loop, vb would add a line to a text field, then make a new line? this means it would have to not replace what's in the text field, but add to it, and it would need to make a new line. this could be a label, it doesn't matter. for example:

Code:for pintcounter 1 to 10
'add the value of pintcounter to the text field/label
next

Code:result:
------------
1
2
3
4
5
6
7
8
9
10
------------

the result is in an ascii multiline textbox/label, btw : )

thanks!

note: i have barely any experience with for loops in vb, so this example could be totally messed up.

Edited by - iamthejake2000 on 2/7/2005 4:32:20 PM

Adding Field In A Active Report And Make It Run
hello i'm a newbie in Visual Basic and I would like to ask about active report in the report that I've made there are four fields, i've add two fields in it but everytme i try to make it run the records that should be in it is do not appear what would be the problem?

any help would be appreciated

Gave Up On ADO And VB - Help Please In Adding New Field To A Table In Access
i am an OK VB programmer but not so good with ADO and VB; just not able to create a filed in an existing database;

1. database is MS Acces (.mdb file);
2. there are 4 talbles in this database file;
3. one table is called "Location";
4. "location" table has 6 fields;
5. I want to add 7th field called "Country" to this table;
6. I am using ADODB connection object to connect to the Access database;

can any one help me with an example on how to add a field to this database;

**i knew how to do it with DAO (there is an object called "database" in DAO; but ADO doesnt have that)

Appreciate your help;

thanks

Adding Field Data To Table (Access 97)
Hi

I want to add a column of sequential numbers (1,2,3....) to a table (95000 records), i tried doing this by adding field in table design & selecting "Autonumber" data type, however get err msge "File sharing lock count exceeded. [Error 3052]".

Is this because too many records?

Would using code avoid this err msge? How do i add numbers to this field "Index" using code, i know how to add rows but cannot get it to add data to specific field.

Your help is much appreciated!

Rgds,
Muddy

Adding New Database Field In Crystal Report 8
how could I insert a new database field in crystal report 8?

This is my problem goes... I have an existing report which it needs to insert a new field(not in my existing database fields). my CR database is Field Definition(salesman.ttx) which I modified the file and inserted with a new field.

the problem is when I call the CR from my vb program, it doesnt display my additional field. but when I created another report it appeared. is that the way CR8 works?

please help me.

thanks in advance

Adding Field To Recordset Before Showing Datareport
Hello,

I want to set up a datareport that shows something like this (simplified) :

PlaceNumber    Name    Points
---------------------------------------------
   1        John     10
   2         Paul     9

etc.

I have my recordset made up like this :

Code:Option Explicit

Dim strSQL As String 'string die SQL bevat.
Dim rsADODagKlassement As New ADODB.Recordset

Private Sub DataReport_Initialize()

    'SQL string creëren
    strSQL = "SELECT tblPlayer.txtNaam, tblPlayer.txtVoornaam, tblPlay.intPlayNumber, "
    strSQL = strSQL & "tblPoints.intFives * 5 + tblPoints.intFoures * 4 + tblPoints.intThrees * 3 + "
    strSQL = strSQL & "tblPoints.intTwos * 2 AS intTotaal, "
    strSQL = strSQL & "tblPoints.intFives , tblPoints.intFoures, tblPoints.intThrees, "
    strSQL = strSQL & "tblPoints.intTwos, tblPoints.intDefense "
    strSQL = strSQL & "FROM tblDate, tblPlay, tblPoints, tblPlayer "
    strSQL = strSQL & "WHERE tblDate.intDateID = tblPlay.intDateID AND "
    strSQL = strSQL & "tblPlay.intPlayID = tblPoints.intPlayID AND "
    strSQL = strSQL & "tblPlay.strRijksregister = tblPlayer.txtRijksregister AND "
    strSQL = strSQL & "tblDate.intDateID = "
    strSQL = strSQL & gmintDatumCodeRapporten & " "
    strSQL = strSQL & "ORDER BY tblPoints.intFives * 5 + tblPoints.intFoures * 4 + tblPoints.intThrees * 3 + "
    strSQL = strSQL & "tblPoints.intTwos * 2 DESC, tblPoints.intDefense DESC "

    'Recordset creëren
    Call CreateRecordset

    'rapport linken
    Set repDagKlassement.DataSource = rsADODagKlassement

    'Datum op rapporthoofd plaatsen
    Me.Sections("ReportHeader").Controls.Item("lblTitle").Caption = "Dagklassement van " & gmstrDatumRapporten
    Me.Sections("PageFooter").Controls.Item("lblPageFoot").Caption = "+++ " & gmstrDatumRapporten & _
       " +++ Shooting Team Oostende"
End Sub

Private Sub CreateRecordset()
    'Gehele tabel openen
    With rsADODagKlassement
        .CursorType = adOpenKeyset
        .LockType = adLockOptimistic
        .Open strSQL, gmcnnADOShooting, , , adCmdText
    End With
End Sub

Now I want to add a field to this recordset where i can populate the placenumbers.

Can anyone help me with this ???

Or is it possible to just place code in datareport_Initialize that places a number for each recordline in the report ???

Hoping anyone can help me ???

RoCa Greetz U