Variable length records Results


Would it be possible for someone to explain to me how to program a variable length text fields in a table, as opposed to a fixed length field?

Also - what would be the disadvantage of using this type of field ?


I have a sub routine that add news records to an AssignedBatches table based on criteria in a function. The function returns a variable length array of batch numbers that are to used in assigning batches. I've got this part working well. Now I need to show the user the added records. My understanding is that Access SQL does not allow variables in teh criteria, but does accept functions.

I have the batch numbers stored in a Public Function called GetBatchNumbers() within the current form. I created a query detailing all the fields the user needs to see with the criteria: Where MasterBatch.BatchNum = GetBatchNumbers()

In a sub routine within the current form, I am opening the query using:

docmd.OpenQuery "qryAssignedBatches", acviewnormal, acReadonly

I am getting a runtime error (3085): undefined function 'GetBatchNumbers' in

I don't think it's the SQL syntax that's causing the error. Is my
problem that I am opening the query from within a subroutine & it can't see
the function data? Or do I need to have the function in it's own module for
this work?

Perhaps a temporary table to store the batch numbers is the way to go?

I could use some help!

I need to be able to display 1 to N records from a table as well as add/update them to the same table. Here’s my story. I have a table. The key is Project and Building. The attributes are several (10) different percentages.

The user wants to be able to enter the Project number and display all the Buildings associated to that Project. There can be 0 records or there can be X records. There is no limit to the number of Buildings for a Project. The user wants to be able to update, delete or add Buildings to the Project. They also do not want the database updated until they click an ‘Update’ button therefore, I cannot bind the table to the form, I think. Can anyone point me in the right direction on how I would even begin defining a form that will grow depending on how many records are in the result set and how I would process the records once the ‘update’ button is clicked. Thanks for any help.

Hi all

I am compiling a report which will have several pages (about 12) of text containing variable data followed by a variable length section of 1 record per page and finally a conclusion, again with variable data.

The report builder seems to restrict me to 55cm in the report header which doesn't equate to anywhere near 12 pages. Is there a way of getting around this?


Hello All and Happy New Year!

I am working on a project where I need to create a record from a subsubform while referencing primary key data from the parent form. I am using the Link Master/Child Fields to filter the data on the subform. I would not have to use the subsubform except my subform exceeds the 22" limitation. I broke the form in to parts on a tab control. To keep the form from auto-scrolling to the first input field, I created a subsubform on each of the two tabs. There are no data fields on the subform. (I have tested the fields there and the record is created correctly.) I need help setting up the parent child links that will allow me to associate the subsubform back to the parent form.

I first tried to use the same data source and child/master link fields for both the subform and the subsubform, I got an error saying the record could not be created without a primary key. I tried a number of other configurations unsuccessfully. Now I get an "Object variable or With block variable not set" error when I try to define the link master/child fields on the subsubform.

As a side note, I cannot eliminate the subform because it also has a tab control with multiple subforms on it. My issue exists with this tab due to the length of the information I have to present.

Thanks in advance!

I have a report which has five columns; one of them is a text field of variable length, there's the odd record here and there where the text is long enough to take up more than one line, so I set the CanGrow property to true, fine, except that the fields have visible borders and are all butted up tight together to make a grid, but when the text field grows, I get a gap underneath the other fields, even if I set them all to CanGrow.

Is there a way to force the other fields to grow, even though they don't need to, along with the one that does?



Quick question on VBA Access 2003/07,
I have a VBA class module that loads a single record from 1 of 4 tables (via a main form and datasheet subforms) to another form.
Most of the fields are keys however 1 field is memo data type.

What data type is best to use for the variable / property procedure in the class module. String seems to work but are there any limits in string length in vba, or a better way of achieving this. I seem to remember reading somewhere that memo datatype fields the data is not stored in the access db only a reference, is there any way of using this and passing the reference to the text box

Also the data is passed to the form via an ADO recordset am I right in saying that there is no issue with string length in ADO recordsets?



I am scanning a bar code into a textbox called txtBar. After each scan, a record is created and other code is executed. Then the txtBar textbox is cleared and the focus is automatically returned to txtBar. Currently all bar codes are of length 15 and it works perfectly using the On Change event. We are now moving to a 19 character bar code. During the transition period some bar codes of both lengths will have to be scanned. I can no longer use the On Change event with variable length tickets. I am attempting to code it so that when the user presses Enter or some other key, the code will check the length of the bar code and perform the correct code. The following code currently works for scanning 15 character bar codes only:

	Public Sub txtBar_Change()
  If Len(txtBar.Text) = 15 Then
     'code for extracting characters from bar code, querying
     ' database and creating a record
     txtBar.Text = " "
  End If
End Sub

After switching to 19 character bar codes, it will look something like this:

	Private Sub txtBar_Enter()
  If Len(txtBar.Text) = 15 Then
     'code for extracting characters from bar code, querying
     ' database and creating a record
     txtBar.Text = " "
  If Len(txtBar.Text) = 19 Then
     'code for extracting characters from bar code, querying
     ' database and creating a record
     txtBar.Text = " "
  End If
End Sub

After I scan a bar code, the cursor tabs to the next textbox on the form. If I then click back on txtBar the above code is executed and does what it is supposed to do, but I don't want the user to have to click back into the textbox.

I would like the focus to stay in txtBar and have the user press Enter or another key to execute the above code. I have tried txtBar.SetFocus in the After Update and On Exit events, but it still tabs to the next field.

Does anyone have any suggestions? Is there another event or another way to get the results I want?


Hi everyone,

This is my first post, and I wanted to say thank you for all your help already! I've been browsing for weeks, learning a lot. To my question:

I'm looking for some opinions as to how to approach the following situation.

I have a table designed as such.

ID , Code , Length , Cost
2540 , 75 , 34 , 2.21
2541 , 75 , 36 , 2.25
2542 , 75 , 38 , 2.30
2543 , 75 , 40 , 2.45
2544 , 76 , 2 , 0.10
2545 , 76 , 4 , 0.15

Where I would like to use VBA to interpolate a cost for a known Code (i.e.: 75) and a variable Length (for ex: 38.55) using the data stored in this table. I'm having some trouble figuring out a robust approach to this situation, because I'm new to Access.

I've been reading a lot about Domain functions and DAO Recordsets, and would like some input if you have a minute. I'd like to be able to know the minimum and maximum length listed for an ID, as well as design an algorithm that can deal with a table that's not necessarily in perfect order. Someone may choose to add the following record at an arbitrary location for instance..

ID , Code , Length , Cost
2546 , 75 , 44 , 2.65

Thanks for any suggestions or advice you can give! I'm getting there.. but am somewhat confused by DAO, etc.

Hi all,

In my database, one of the key records is identified by it's "Clause Number" as it appears in the document. Common convention is to number these as in the title example ( however the number of levels of numbering changes even within a document - only the separator '.' stays the same.

I currently have it set up to A-Z sort on the Clause Number (a text field to allow the format to be maintained, but also the odd occasion where a document uses titles rather than a numbering system). As well as for items such as titles rather than numbers, this approach is limited when the numbers in between the separators hit double digits.

To counteract this I have added a 'manual order' integer field to be used if necessary, and this order supercedes the order of the Clause Number in every section of my database. I'm now setting up a form to allow the user to re-order the items more easily where my initial thinking was 'number the "Manual Order" field in the same order as it is sorted to begin with and then let the user move records up/down as they please.

However it would be more user-friendly if I could create a button to read the format and order based on that (rather than a user click 10+ times to move each and every item falling under 4.2.xx above 4.10.xx-4.19.xx for example). However I'm not too sure how to do this with variable lengths (i.e. potentially unlimited separators).

Does anyone know how to set this up? The way I see it there needs to be two sections to this code:Convert the numbers between separators into integer variables (perhaps a prepatory step of counting the number of variables required?) Arrange the Clauses in order of left to right, once complete assign a number in the current order. The main problem I foresee is the 'variable' number of variables being assigned.

Any help or alternative solutions are greatly appreciated!

Kind regards,



Im wondering about two things here. I have the following code setup to run. The problem is that when I increase the length of the strmessage variable past a point the send button stops working


Dim rsEmail As DAO.Recordset 'the recordset with the email addresses to be used
Dim strEmail As String 'the email address
Dim strMessage As String 'message to be sent
Dim strSubject As String 'subject of message
Dim strOrderNum As String 'the web order number
Dim intEmail_Sent As Boolean
Dim strBillFirstname As String
Dim strBilllastname As String
Dim strShipFirstname As String
Dim strShipLastname As String
Dim strShipCompany As String
Dim strShipAddress As String
Dim strShipAddress2 As String
Dim strShipCity As String
Dim strShipProvince As String
Dim strShipPostalCode As String
Dim strShipCountry As String

Set rsEmail = CurrentDb.OpenRecordset("tmp_email") 'opens table with email addresses
On Error Resume Next 'skips email addresses with errors

Do While Not rsEmail.EOF 'loops through until the end of the table
strEmail = rsEmail.Fields("BillEmail").Value 'sets email address value
strOrderNum = rsEmail.Fields("OrderID").Value 'sets job number value

strBillFirstname = rsEmail.Fields("BillFirstName").Value
strBilllastname = rsEmail.Fields("BilllastName").Value
strShipFirstname = rsEmail.Fields("ShipFirstname").Value
strShipLastname = rsEmail.Fields("ShipLastname").Value
strShipCompany = rsEmail.Fields("ShipCompany").Value
strShipAddress = rsEmail.Fields("ShipAddress").Value
strShipAddress2 = rsEmail.Fields("ShipAddress2").Value
strShipCity = rsEmail.Fields("ShipCity").Value
strShipProvince = rsEmail.Fields("ShipProvince").Value
strShipPostalCode = rsEmail.Fields("ShipPostalCode").Value
strShipCountry = rsEmail.Fields("ShipCountry").Value

strSubject = "Lonley Planet Publications LTD Shipping Notification. Your Refernence: (" & strOrderNum & ")"
strMessage = "Dear " & strBillFirstname & " " & strBilllastname & "," _
& vbCrLf & vbCrLf & "We are please to inform you that your order: (" & strOrderNum & ") has been dispatched to:" _
& vbCrLf & vbCrLf & strShipFirstname & " " & strShipLastname _
& vbCrLf & strShipCompany _
& vbCrLf _
& strShipAddress _
& vbCrLf _
& strShipAddress2 _
& vbCrLf _
& strShipCity _
& vbCrLf _
& strShipProvince _
& vbCrLf _
& strShipPostalCode _
& vbCrLf & strShipCountry & vbCrLf _

DoCmd.SendObject , , , strEmail, , , strSubject, strMessage, False 'sends emails using above parameters
rsEmail.MoveNext 'goes to next record
Loop 'starts whole process over again
Set rsEmail = Nothing 'closes table
'End If
'DoCmd.Close 'closes form when completed
End Sub

*** If I try and add a extra line with just a text comment the code stops working, but if i delete the line the code works again. Its as though there is a max lenth on the message text I can assign. Anyone know how to beat this??


I’ve done a search and cannot find any similar queries!

I’ve a table that varies in length on each import. I want to divide it by a variable number in say a list or combo box and return the required no of records.

i.e. table length is 120 so divide it by 10 and return the first 12 records or
Table length is 150 /10 it returns the first 15 records.

Basically I’ve a Cycle Count and need to divide the parts to be counted by the number of people available.


I have used the following code to create a duplicate record when up-issuing a certificate:

Private Sub DuplicateRecord_Click()

Dim sReferenceNumber As String
Dim sProject As String
Dim sItem_Name As String
Dim sItem_description As String
Dim sPart_Number As String
Dim sManufacturer_ID As String
Dim sExemptionNumber As String
Dim sExemptionDeviationCode As String
Dim sExemptionDeviation_Statement As String
Dim sNotes As String
Dim sIssueNumber As String

sReferenceNumber = ReferenceNumber
sProject = Project
sItem_Name = Item_Name
sItem_description = Item_description
sPart_Number = Part_Number
sManufacturer_ID = Manufacturer_ID
sExemptionNumber = ExemptionNumber
sExemptionDeviationCode = ExemptionDeviationCode
sExemptionDeviation_Statement = ExemptionDeviation_Statement
sNotes = Notes
sIssueNumber = IssueNumber

DoCmd.GoToRecord , , acNewRec

ReferenceNumber = sReferenceNumber
Project = sProject
Item_Name = sItem_Name
Item_description = sItem_description
Part_Number = sPart_Number
Manufacturer_ID = sManufacturer_ID
ExemptionNumber = sExemptionNumber
ExemptionDeviationCode = sExemptionDeviationCode
ExemptionDeviation_Statement = sExemptionDeviation_Statement
Notes = sNotes
IssueNumber = (sIssueNumber + 1)

sReferenceNumber = ""
sProject = ""
sItem_Name = ""
sItem_description = ""
sPart_Number = ""
sManufacturer_ID = ""
sExemptionNumber = ""
sExemptionDeviationCode = ""
sExemptionDeviation_Statement = ""
sNotes = ""
sIssueNumber = ""

Exit Sub

MsgBox Err.Description
Resume exit_DuplicateRecord_Click

End Sub

Everything works fine with the exception of those in red. It doesn't copy across the textassociated with these fields. The only difference that i can see from the other fields is that Deviation Code is a text box but set to 255 (whereas the other text fields are set to 50) characters and Deviation Statement is a memo, so could it be the size of the fields?

How can i arrange it such these fields are copied across as well.



In my code I evaluate the number of records in a table and place the value in a variable called Record_Count.

I now wish to attach a string of 0's in front of the Record_Count so that the total number of numbers in x is 10 e.g. 0000158458

Record_Count = 158458
x= String(10 - Len(Record_Count), "0") & Record_Count

However the value of Len(Record_Count) always equals 4! Can anyone suggest how I can change the code so that I obtain the correct length of Record_Count?

Not sure if I'm using the correct terminology here...Basically, I am using a search form to build an SQL statement "on the fly". The resulting statement is a variable named Mysql. What I have discovered (by using the debug window) is that if I put in lots of different criteria in my search form, some of the SQL statement stored in mySQL looks like its been cut off, and the records returned are not what has been specified in the search form. I tried to look up storage size of a string in the help files and all it says is "length of string." So, is there a limit on how long a string a variable can hold, and if so, what can I do to get around this? This will turn into a big problem if I can't figure this out!

Thanks in advance for any input...

[This message has been edited by LQ (edited 10-17-2001).]

here's a funny one

I think it demonstrates that although access may appear to consist of tables and the likes - this is all done by very advanced processing of the real internal data structures in a .mdb file


my client had some problems, which was faulty programming, down to me - fixed and reinstalled, but it then came up with a spurious "record is deleted" error. This turned out to be a "record is deleted" tag in a memo field on a single record - the record could be edited and saved, EXCEPT for this particular memo field within it. So I presume that the memo fields (maybe all strings, because they are variable length) are linked to the rest of the data in a given table using some pointer structure.

No other users were in the database, but it still wouldnt compact/repair because of this or some other spurious error - I think it maybe could have been cleared by shutting down and rebooting the server, but this wasnt possible.

Eventually I decided to recreate a blank database and import everything from the old version. The corrupt record wouldnt import, so I deleted that, and recreated it in the new database.

I then found another table which wouldnt import, again due to spurious corruptions, But I was able to copy and paste that manually.

So at the end I got a cleaned database, which replaced the corrupted one.

It just shows that there really is a lot going on under the skin of an mdb, that just isnt apparent from the external appearance.

I have created a unit converter and I have a text boxes that output weight or length based on the calculation below. However, sometimes the text boxes won't display the calculated value despite clicking on the solve button. Could it be an issue with multiple users writing to the same record at the same time? How can I prevent this? I intended for this to be a single record form.

I tried changing the Weight and Length variables to custom properties as shown here:

How should I redesign this database to make it into a single record database yet not have multiple users editing the same record at the same time? These two things sounds conflicting no matter what! Would it solve the issue if I split it into a BE and FE?

	Private Sub Solve_Click()
'Conversion Equations
        Select Case inputType
        'Case Statement ft^2/lb/mil -> [lb] / [ft]
            Case Is = "ft^2/lb/mil"
            'Solve for weight [lb]
            If outputType = "lb" Then
                Weight = Round((vsheet * widthTemp * Length / matFactor * (Thickness / 0.001)), 2)
                WeightUnit = "lb"
            ElseIf outputType = "ft" Then
            'Solve for length [ft]
                Length = Round((vsheet * matFactor * Weight / (Thickness / 0.001) / widthTemp), 2)
                LengthUnit = "ft"
                MsgBox ("Unknown Input Type")
            End If
        End Select

This may not be the correct forum for this question. If not, I apologize.

I have a database on a drive that is available to multiple users. I want to do this:

A user selects options on a form and clicks Submit. This creates an SQL Query (via VBScript) that varies in length depending on the options selected by the user. The SQL query then populates a recordset that is global for each user's session (in other words, if two users have different queries, then each of their recordsets will be different, but they can access their recordset from any form or report). The recordset will be used as a datasource for a subform, so the user can interact with each record in the recordset.

I already know how to create the variable length SQL Query depending on options selected and open the form/subform afterwards. What I don't know is (1) how to make a recordset global, (2) how to make this recordset available to the forms/reports in the database, and (3) how to make the recordset so each user has their own (in other words, so one person's query doesn't affect another person's recordset).

Thank you for any help you can provide,

Steve Geller

I am trying to change a field lenght of a large database (access 97) It is at 50 right now and want to change it to 25. When I try to change it and save the table I get a error when the bar is about 3/4 the way accross the bottom.

Quote: Microsoft can't change the data type - there isn't enough disk space or memory. then

Quote: not enough space on temporary disk This is error 3183. In the help it says that the TEMP DOS enviroment variable location doesn't have enough space. (summerising). Now I have a 100+gb drive that isn't close to being full. I also changed the MaxLocksPerFile registry dword to 8,000,000 as my table has around over 3 million records. I have tried this on a xp and 2000 machine same results. Next I am going to try it on a 98 machine. I could do it by breaking up the table into 2 differnt ones and do each one individually but there is an autonumber field used as a reference number (I know I know). So that would screw up the numbering scheem (Yes I know there are ways arund that also). this database is about 400MB

Couple of questions
1. Is there a way around this? I have not found a solution on line yet.
2. Will lowering the field length make the database smaller (the data that is in there now is less than 25 charecters and the field is set to 50.) I have gotten mixed info on this. there are a few fields I would like to reduce.



I am pretty new to Access so please excuse me if my error is total muppetry.
When running an INSERT INTO query in Access 2007 VBA I keep getting the error "Microsoft Access can't append all the records... due to key violations. I have read a few threads and cannot find the problem.

Here is the code that hangs. It has worked before and now fails after synching across machines.

SQL = "INSERT INTO Fleet ( ModifiedRegion, seatcat, YearEnding, Fleet )" & _
"VALUES ('" & region & "', " & seatcat & ", " & MyYear & ", " & MyFleet & ");"


All of the values are variables and all have something in them and all are of the right data type. I have tried running

The destination table resides in a backend database in the same folder and has the primary key ID that is an autonumber field. No fields 'required' and all text fields allow zero length. I have created a custom index on the fields ModifiedRegion, seatcat and YearEnding. For this index, primary key and unique are set to 'No' and Ignore Nulls is set to 'Yes'. I have tried deleting the key but it makes no difference. The table does not have relationships and referential integrity is not enforced.

Any ideas appreciated.

Not finding an answer? Try a Google search.