Go to existing record Results

I have been tasked with coming up with a way to automate reconciliation of fish-ticket data in two databases. One database is administered by WA State Dept. Fish & Wildlife (no ability to access this myself) and the other by my employer (Lummi Nation). Both databases should contain identical information (yeah right) since each organisation gets duplicate copies of fish tickets produced by wholesale buyers whenever they buy fish/shellfish.

I can get a summary table in excel from both databases. I have imported them into access as tables, and done select queries to sort the records in ascending order by fish "ticket number" and then by date, species, weight landed, etc.

Each fish ticket number may have more than on record in the table (e.g., a fisherman might catch 200lbs of Chinook and 100 lbs of Coho salmon and both are reported on the same fish ticket.

So, there are three problems I need to be looking for in each table: 1-tickets that are recorded in one db but not the other, 2-records from a ticket that have no matching record in the other database (eg., 4 records in the tribal db and 5 records in the state db for the same ticket number), and 3-when parallel records do exist, are there any significant discrepancies in the recorded amounts of some of the fields for that record?

The tables are quite large (~14,500 records for one year of data) and need to be reconciled reasonably frequently during fisheries to avoid going over quota. The scope and time-consuming nature of this task if done manually has meant that, in practise, no reconciliation has been attempted in 4 years!

I have had a crack at using vba to do this task but without much success. One approach I tried weemed to be working on some sample tables (~300 records) but at about halfway through the process it kept coming up with a no current record error that I couldn't track down. I think it was a result of too much recursive activity between the functions.

I redesigned my vba approach to try to do more with do loops rather than calling functions but now I'm stuck in a loop and was wondering if someone might help me spot my problem?

What I am trying to do with this script is:
make 2 recordsets based on the ordered queries of the state and tribal tables
start at the beginning of each recordset
check the current ticket number against a table of unpaired ticket numbers (built from queries beforehand)
if they do not appear then we can exclude error type 1 and compare the actual ticket numbers.
if the numbers do not match, then we likely have an error of type 2. The record with the lower ticket number has to be exported to a table, and we move to the next record in that recordset and restart the pairwise comparison of ticket numbers.
If the ticket numbers do match, then we compare the data in each field for a type 3 error. If all is the same then we move on to the next record in each recordset and start over. If not, then we export the disagreeing records to suitable tables, and move to the next record in each recordset and start the comparison over again.
If we come to the end of a recordset, then the remaining records in the other trecordset (if any) are to be exported to the appropriate table.

I've attached a copy of the db. The code is linked to a command button on a form that should open at startup. The db is in Access 2000 format. I've tried to comment the code to explain what I'm trying to do.

So, after that long-winded explanation, I'd greatly appreciate any help that may be offered. I'm relatively new to vba and doubtless am making some error(s) that I just can't see.

After searching, I'm not too sure how I want to code this:

On a form, there will be three unbound fields for contacts with their corresponding comboboxes. Whenever user saves the record, the code takes the fields and combobox selections and insert into contact table. Presently the code works just fine.

However, I need to be able to input existing contact info when user navigator old records both in OnOpen and OnCurrent event. I want to do:

1) retrieve up to three contacts (there may be more than three saved in the table), going from the most oldest to most recent using the PK.

2) ensure that at least one contact is an E-mail type if there is such.

The #2, I could do. However with #1, I'm not sure if I want to use DMin() or open a recordset and using .FindFirst to lookup contacts for a given clients.

Which would be best for me?

Hey guys, please could you help me with a couple of things i'm stuck with.

Firstly, i have created a module (dll) which stops access scrolling through records with the mouse scroll wheel. This works, however it has problems. When i use it on my subforms, it works as is supposed to however it stops me being able to click into the various fields. I can tab to them, just not click in them. Any reason as to why? Or another way of stopping it scrolling without this problem?

The second problem is that the database i am creating is to be shared over a network, so multiple users. Firstly, how do i go about allowing more than one person to open it at once, and secondly is locations. On one of the main forms, there is a picture field. Under this, is a field with a file location. When a user opens the 'open file dialog' window and selects a picture, the pictures location is put into the field, and the picture field is updated with the new picture, thus displaying it. This is all well on the local machine, however, if it is on a network, it will not work because the file specified on one machine, does not exist on another. How to i get around this? So i am able to add a picture to the member of staff, and view it from every computer that can access it?

Thanks for your help in advanced

I will have three combo-boxes which the users will select in order (suppose to atleast). After you select the first is limits the recordsourse of the next etc...
In the second combo-box I use in the query for the rowsource:
[Forms]![frm_MyForm]![cbo_Primary]. I think this is the best way to do it.

But, if I go back to the first combo box and select a different value the displayed value of the second combo-box does not change. If I open the drop down the records looks to be right. I would like the value of the second combo-box to switch to the first record of the new rowsouce if the current one displaying does not exist in the new rowsource.

Any help?

Hi ,

I am using Access Application with Orcale Db as backend and this application is already in production.
The problem is.. USers Complained that the system is slow, this is because there is refresh (requery) for each Insert (When they click submit, that particular record is inserted and then a refresh has to be done to bring only unused member IDs on the User screen), i had used a Sql query using NOT IN , then i realized and now changed it to NOT EXISTS, but with this new query , if i test recordset. EOF , even though the query returns more than 13000 ..records, EOF sets to TRUE and the other part (not supposed to) gets executed. I really cant figure out why, i took the same query put it in query builder in Access and ran it , it gave 13000 records..but in VBA , this recordset is not giving expected results and so..i am totally confused..The part of the Code is shown below.

newquery = " SELECT A.MEMBER_ID, A.MEMBER_NAME, A.ADDRESS_LINE1..................

rst1.Open newquery, Cnt, adOpenDynamic, adLockOptimistic
If rst1.EOF = False Then
MsgBox " No records to Process!"
End If

EVEn though it should requery , it gives a message No records to process which is wrong. It works with NOT IN.... BUT VERY SLOW, NOT EXISTS IS FAST BUT DOES NOT GIVE ME WHAT I WANT..

Where am i going wrong..help pls!!!!



I have a form on which I have a string field called "Ringnumber" which does not accept duplicate values. Everything works fine with my self-created error message in the "Before update" event of this field.
But now here's the problem.
When I fill in this field and don't leave this field but instead I click immediately on the cross in the upper right corner to close the form I first get my own message followed by the following message : "You can not save this record, do you want to close anyway". When I click no I go back to the form, when I click yes the form closes.

Now, how can I prevent that this second message (You can not save this record ...) shows up?

My self-created error message :

Private Sub Ringnumber_BeforeUpdate(Cancel As Integer)

Dim NewRingnumber As String
Dim strCriteria As String
Dim rs As Recordset

Set rs = Me.RecordsetClone

NewRingnumber = Me.Ringnumber
strCriteria = "[Ringnumber]=" & "'" & NewRingnumber & "'"

If DCount("Ringnumber", "tblBirds", strCriteria) > 0 Then
Cancel = True
MsgBox "This bird already exists", vbInformation, "Double ringnumber"
End If

Set rs = Nothing

End Sub

Thanks in advance.

Could somebody please help me before I dump a gallon of water on my pc!

I am very happy to have come across this site on the internet. TheScripts has helped me through so many issues to this point. I have now spent the last 2 days scouring the net for a resolution to my problem.

I have a DB with MANY tables, but a transfer of 1 memo field between 2 in particular (tbl_Proposal to tbl_Job) is where my problem is. Nothing is working. The field is a proposal which can have next to nothing up to several pages (including carriage returns).

I have left out all the code around this that I know works. The database has been in effect for several years, but is in the process of a major enhancement so there have been no issues with any of the existing globals (g_variable).

Any items that are a global (g_*) are functioning properly. They are passing the proper values in the proper formats.

Here is a snippet of feeble attempt number 1:

	g_NewJob = True        ' Flag to control a caption in the form popup_Date
  DoCmd.OpenForm "popup_Date", acNormal, , , , acDialog
  If IsNull(g_Accepted_Date) Then
    Exit Sub
  End If
  If Trim(g_Accepted_Date & "x") = "x" Then
    ' User entered a blank or hit cancel
    DoCmd.SetWarnings True
    Exit Sub
  End If
  'With Bid Description 2
  DoCmd.RunSQL ("INSERT INTO tbl_Job (Job_Number, CustomerID, ContactID, ProposalID, Proposal_Date, Proposal_Revision_Number,
Job_Scope_Of_Work, Job_Name, Job_Address, Job_City, Job_State, Job_Brief_Description, Job_Accepted_Date,
Job_Pictures_Directory, Job_Excel_Link ) " & _
                "VALUES('" & g_JobNumber & "', " & Me.CustomerID & ", " & Me.ContactID & ", " & Me.ProposalID & ", #" &
Me.Proposal_Date & "#, '" & Me.Prop_Revision_Number & "', '" & Me.Bid_Description2 & "', '" & Me.Job_Name & "', '" &
Me.Job_Address & "', '" & Me.Job_City & "', '" & Me.Job_State & "', '" & Me.Job_Brief_Description & "', #" & g_Accepted_Date
& "#, '" & Me.Pictures_Directory & "', '" & Me.Excel_Link & "')")

Run-time error '3075':

So, I came to the conclusion that because some of these memo fields (Bid_Description2) can be large, Access is having a serious issue with passing text of that size in a single query.

Next, I found a solution that recommended parsing the data using an array. I used the carriage returns as a delimeter to parse the memo in to smaller chunks (fingers crossed).

Feeble attempt number 2

	'Without Bid Description 2
  DoCmd.RunSQL ("INSERT INTO tbl_Job ( Job_Number, CustomerID, ContactID, ProposalID, Proposal_Date,
Proposal_Revision_Number, Job_Name, Job_Address, Job_City, Job_State, Job_Brief_Description, Job_Accepted_Date,
Job_Pictures_Directory, Job_Excel_Link ) " & _
                "VALUES('" & g_JobNumber & "', " & Me.CustomerID & ", " & Me.ContactID & ", " & Me.ProposalID & ", #" &
Me.Proposal_Date & "#, '" & Me.Prop_Revision_Number & "', '" & Me.Job_Name & "', '" & Me.Job_Address & "', '" & Me.Job_City &
"', '" & Me.Job_State & "', '" & Me.Job_Brief_Description & "', #" & g_Accepted_Date & "#, '" & Me.Pictures_Directory & "',
'" & Me.Excel_Link & "')")
  Dim myString
  Dim x As Integer
  Dim myArray() As String
  g_SQLStmt1 = "SELECT tbl_Proposal.Bid_Description2 FROM tbl_Proposal WHERE (((tbl_Proposal.ProposalID)=" & Me.ProposalID &
  Set g_myRS1 = g_myDB.OpenRecordset(g_SQLStmt1)
   With g_myRS1
    If .EOF And .BOF Then
        MsgBox "No Scope of Work present"
        GoTo LetsMOveOn
        myString = g_myRS1("Bid_Description2")
    End If
   End With
  myArray = Split(myString, Chr(13) & Chr(10), -1, 1)
  For x = LBound(myArray) To UBound(myArray)
    DoCmd.RunSQL ("UPDATE tbl_Job SET tbl_Job.Job_Scope_Of_Work = [tbl_Job]![Job_Scope_Of_Work] & '" & myArray(x) & vbCrLf &
"' WHERE (((tbl_Job.Job_Number)='" & g_JobNumber & "'));")
    '  Somewhere in here it died
  Next x

This was working sweet UNTIL about half way through the line of text in the 9th element in the arrray when I received yeat another Run-time 3075 error. This was about 571 characters (including spaces and the CHR's). So, it's not based on the 256 character thing.

OK. Breath deep. Fingers no longer crossed, only one is now extended.
Let's try something else. Feeble attempt number 3

	'Without Bid Description 2
  DoCmd.RunSQL ("INSERT INTO tbl_Job ( Job_Number, CustomerID, ContactID, ProposalID, Proposal_Date,
Proposal_Revision_Number, Job_Name, Job_Address, Job_City, Job_State, Job_Brief_Description, Job_Accepted_Date,
Job_Pictures_Directory, Job_Excel_Link ) " & _
                "VALUES('" & g_JobNumber & "', " & Me.CustomerID & ", " & Me.ContactID & ", " & Me.ProposalID & ", #" &
Me.Proposal_Date & "#, '" & Me.Prop_Revision_Number & "', '" & Me.Job_Name & "', '" & Me.Job_Address & "', '" & Me.Job_City &
"', '" & Me.Job_State & "', '" & Me.Job_Brief_Description & "', #" & g_Accepted_Date & "#, '" & Me.Pictures_Directory & "',
'" & Me.Excel_Link & "')")
  g_SQLStmt1 = "SELECT tbl_Proposal.Bid_Description2 FROM tbl_Proposal WHERE (((tbl_Proposal.ProposalID)=" & Me.ProposalID &
  Dim sDriver As String
  Dim workdb As Database
  Dim worktable As DAO.TableDef
  Dim workset As DAO.Recordset
  Set workdb = CurrentDb
  Set worktable = workdb.TableDefs("tbl_Job")
  Set workset = worktable.OpenRecordset(dbOpenTable)
  Set g_myRS1 = g_myDB.OpenRecordset(g_SQLStmt1)
    With g_myRS1
  If Not workset.EOF Then
    sDriver = CStr(g_JobNumber)
    workset.Index = "PrimaryKey"
    workset.Seek "=", sDriver
    If workset.NoMatch Then
      MsgBox ("Entry not found")
      MsgBox ("Entry found")
    End If
    MsgBox "No records."
  End If
  End With

Alas, a new run time error : 3219

Can anyone please help me? I am at my wit's end and I have a bottle of Poland Spring and I'm not afraid to use it...
Laughter is the only thig that keeps me going at this point.


Hi all. This will be my first post on these forums.
I've searched and browsed through a number of really detailed support threads, most of which were far over my head, and I haven't been able to find a solution that I can apply to the specific issue I am facing.

Here is the situation I am in. If anybody can help, it will be most appreciated.

I am working with an external client who currently uses Microsoft Excel (2000) to manage a series of spreadsheets for their own customer accounts. They have a few hundred of these documents where they track the specific items a customer has purchased, and the most current prices of these items. The spreadsheets are printed and sent out to the customer on a monthly basis so they have a record of the most recent prices for each item.

Currently, this client spends many hours at the end of each month manually going through all these hundreds of Excel spreadsheets and updating the items with the increased pricing. There is a lot of redundant clerical work involved, and that's where I come in.

I have been asked to provide an automated solution to help cut down on the amount of work needed every month to update these existing spreadsheets. The criteria for updating the spreadsheets is pretty simple, and I have the logic of the updates all planned out in my head. However, the actual implementation of this is beyond me.

Here's what I need to do:

1) Take two sources of input data; one is the spreadsheet to be updated, the second is another spreadsheet with a master list of all the items and their price increases for the current month.
2) If an item on the first spreadsheet exists on the second sheet, then it has a price increase and the first spreadsheet needs to be updated based on the increase listed in the second sheet.
3) After repeating this for all the items listed on the first sheet, the sheet should be saved (either as the same file or a new file derived from the original file name) and its formatting preserved.
4) This should be repeated in an automated fashion for all the customer spreadsheets.

My first thought was to program something in C++, but that was beyond my experience.
My second thought was that Microsoft Access might be able to perform this work for me, and I am pretty sure that it can. Here is how I am envisioning the process:

1) The original spreadsheets are imported to temporary transactional tables in the Access database.
2) The necessary comparisons and updates are made within Access.
3) An updated Excel spreadsheet is exported from Access with either the same file name as the original sheet, or a new file name derived from it.
4) Automated rinse and repeat through all the source sheets.

The three problems I'm running into in designing this solution are
1) Access strips off the formatting of the Excel spreadsheet when I import/export. I need the formatting of the original source sheet to be retained.
2) Access removes the image that is imbedded in the original Excel spreadsheet when import/exporting.
3) I have no idea how I would automate it so that the user can tell it to run and it automatically opens their 200+ source sheets and updates them.

If anybody can help shed some light onto this problem, I would be most appreciative.

Thanks in advance.

I want to add an audit trail to my existing database.
Basically when certain button are clicked I want the On Click event to add an entry into tblAudit.

tblAudit contains [Request Number], [Action], [Date] [Time] [User]

I want the request number to be taken from the form field of the same name, Action will be a set phrase such as "Record Updated" and Date, Time and User just stamped e.g. Date() etc.

How would I go about writing the code to insert this record, can it be done though a query more easily?

Thanks for your help

Hi! I'm new to this forum, and new to Access.

I'm attemtpting to convert an application I developed in Excel in order to capture the power Access provides. Given the nature of my data, a relational DB is where I need to be as opposed to the way I've prototyped things with Excel.

I'm hitting a wall though on manipulating Access Forms. In Excel, I make extensive use of UserForms that I manipulate with VBA. Often, I take advantage of the _Change Event, for a Field(eg. TextBox) on the Userform to trigger changes in related fields.

Although the Access Help file refers to a Change Event, it doesn't appear it really exists in the context I'm attempting to use it, thus I've resorted to the AfterUpdate event. However, I'm finding that this event does not fire off when the Value of a Field changes via VBA code; it only seems to fire off through user interaction via the GUI.

Here's what I'm trying to accomplish...as a newbie to Access I'm most interested in any recommendations as to how to achieve my task. Perhaps I've been going about things in a fundamentally wrong manner??

I have three primary tables to define my relationships. Clients, Properties, and Contracts. Each client can own multiple Properties, each property can have multiple associated Contracts.

I've created a Form with a Tab control. In the Form's Header I display the Client Name and Phone number for the record being viewed. The first page of the Tab Conrol contains other fields from the Clients table for the current Record. That all works fine.

It's the second Page of the Tab control where I'm having issues. I've created two Listboxes for this page. Listbox1 to contain all the properties associated with the current client, and Listbox2 to contain all the Contracts associated with the selection in Listbox1. (I don't need to see all of a Client's contracts here, just the one's associated with a given property.)

ListBox1 gets populated with all the Properties associated with the current Client during the Form_Current event by doing a .Requery for the Listbox. After hacking my way through creating Queries, I've got that working fine. From there, I attempt to do a .requery on a Subform that contains various data associated with the Property selected in the Listbox1. This is where I'm having timing issues. It's not until I click Litbox1 with the mouse that I am able to successfully populate the subform. I do this through the Listbox1_AfterUpdate event, it works fine. however, I'm used to having a _Change event to handle this scenario...the value of the Listbox1 changes (via code), and then other things happen (eg. Requery the subform) I would like the Subform's data to be updated with the associated data to Listbox1's first record, automatically, without having to click the Listbox.

I've disocvered that I can't set the .listindex property of the Listbox via VBA, this was odd to me, coming from Excel. I discovered the .Selected(n) property, and have used this to indirectly set the listindex of the Listbox by using .Selected(0) in the code, thinking this might trigger the AfterUpdate event and thus I could Requery the subform there. AterUpdate doesn't fire until I click the Listbox with my mouse however.

I have more related issues, like populating Listbox2 without having to first manually click a record in Litbox1, but instead have it done during the Form_Current event by using the first record in Listbox1, but I'll leave those for now.

In summary, I need to browse the clients, maybe with the navigation bar at the bottom for starters, and have the new client's first property data, and the data associated with the first contract for the first porperty, appear in the appropiate subforms without having to click the form anywhere. I know this can be done, and I know how to do it with Excel Userforms but I'm a little lost in Access.

I feel that I'm close, but maybe I'm fundamentally off here. Any insight would be much appreciated.

Thanks in Advance!


I am not rightly sure where to start with the logic with this and not exactly an expert either

The outline is I need to update client information in main table with information provided by my colleagues. This information may either exist already or be an additional client.

I have a Primary Table "ClientData" and another table called "TempUpdate" - my goal is to run some code that checks to see if the client code exists in ClientData and if it does run an update query with that record else run an append query for it to add the record. The source of the new data is TempUpdate which would contain many records for updating/adding.

Has anyone any suggestions please on how I would go about such a task, reviewing each row in my temp table to decide whether it should be added or updated?

Thanks in advance for reading this and for any suggestions you can offer,




I was wondering if anybody could help me with this. I have a Database in SQL server, which is quite large with approx 300 tables. This database is a static database and in order to get the most upto date information I can download the database and recieve it as a zip file with an Access Database. The problem I have is how to find away of getting all these tables in the access database into SQL server. I have tried creating a DTS package in SQL server, but the DTS package appends and will therefore not append exisiting records that have been updated, plus it returns an error message because of duplicate records.

HAs anybody got any ideas of a script that I can run to take all the tables in the access database and load all the data into the existing SQL database. Would I need to drop existing SQL tables first, or Delete the contents of the SQL tables, does anybody have any idea how i might go about doing this



Hey guys,

I'm using Access 2003 as a front-end to query an SQLServer2000 database. The form I use to display data has 'New Entry' and 'Edit Entry' buttons, both of which open up the same modal form to enter data. When creating a new entry, everything works fine and the data is added to the correct table. However, if I then open an existing entry and try to edit it, I get the following message when trying to save it back to the database:

'This record has been changed by another user since you started editing it. If you save the record, you will overwrite the changes the other user has made.

Copying the changes to the clipboard will let you look at the values the other user entered, and then paste your changes back in if you decide to make the changes.'

I initially thought it may be a problem with SQLServer, but if I go into the server and manually create an entry, then edit it, I'm successful. This makes me think there is a problem in the way that I've set up Access to create and edit the entry, more specifically, I think that Access is either not closing the record when the new entry is made, or is opening the record twice when I attempt to edit it. I am the only person using this application (or, rather, testing it as it's still in development) so it isn't happening because someone else is trying to edit the same comment. Does anyone have any ideas?

Unfortunately, my boss is the only one with access to the SQLServer machine, so I can't check anything that's happening with that until he returns from holiday on Wednesday, so any suggestions which involve looking at the the server will have to wait until then before I can test them.


Going doolally here.

I've created a simple ADP Access 2007 front end which connects to an MS SQL2000 db.

I want to open a Form which has a source of a stored procedure ( A simple select at this point ) but I'm getting 'The Record Source (SP) doesn't exist on this form.

It opens fine if I just point to the table. Strangley though a report which just looks at the table also doesn't work.

It runs fine using full access 2007 but I need to dispribute this out to a few people. It's not permissions as I can replicate the error myself if I remove Access 2007 and just install runtime 2007.

Do I need some sort of connection string in a module?

Going nuts here! I've done it before with 2003. Can't see why it doesn't work.

I've been asked at work to set up an access database. I'm the most IT literate, but I dont have any experience using Access.
Basically, we need a database that holds the information of our customers.
I've set up a table, with all the fields I require, (name, address, postcode, dates, details, etc), and that's all filled in correctly and all the information is ok. I've then set up a form, so it's a more user-friendly interface for the less PC-literate of the staff, and that seems to be working ok.
I've then set up a second form, with the load page on it, and it has 3 buttons - Add New Case, Search Existing Cases and Edit Existing Cases.
I want it to: bring up a blank form for Add New Case, so I can fill that in and it will add to the table.
bring up a blank form for Search Existing Cases, so I can fill in, say, the name, and it will bring up the first case with that name,and then i can search through them, but the information is read only
bring up a blank form for Edit Existing Cases, so again, I can search and scroll through them, but this time the information is editable.
I have it working correctly for 'Add New Case', that is all going as I want it to, however for Search and Edit, it's bringing up the first record on the table, and I cant search, If i remove the information from the boxes, it's removed from the table.

Also, On the page im currently working on, I want to have:

2 or 3 text boxes at the top of the page, for Sirname, Postcode and Received Date, with a search button next to these.

When I click search, I want this to bring up all the matching cases in a box to the right of this, just in a list.

I then want to be able to click on the case I require, and that will open up in a form further down the page, with all the other information, which also includes address, telephone number, email address, notes etc.

I want a read only version of this, and an editable version of this.

Can anyone suggest a way to do this? I am a beginner at Access so I will need quite detailed information.

I have been working on a personal finance database, and suddenly, I notice that both my tables and queries are invisible. All of them.

I know they are still there because the forms that use both tables and queries are working and generating all of the data. I also know that they exist somewhere locally because my database is stored on a flash drive and everything works regardless of which computer I use.

I need to add some extra tables to the database as well as alter the structure of the tables, but for the life of me, I cannot figure out where everything went, or at the very least why it's invisible.

I'm not completely daft with code, so if VBA is something that could bring back the visibility of all tables, I'd prefer to go that way.

Some other diagnostic flubs that seem to be related (they all started happening at the same time):

I cannot create macros. Whenever I try to save a new one, it suddenly says "Record is deleted". The macro remains in name, but there are no commands that I can add to it, and in addition, when I double-click the macro in order to have it run (and invariably generate an error and halt, because no form is loaded -- I'm okay with that), it does not reach that point and says something like it cannot find the macro.

Thanks for your help.


I am still in my conversion process of updating all existing databases to Access 2007 and I am wondering if there is anything I should be concerned about in regard to the following.
I have a split database with the back end located on the "host", The LAN consist of 4 computers Each cpu has a copy of the front end installed on it's local hard drive via Access 2007 Runtime and Developers Extension A majority of the database is used for producing canned reports and graphs Within the backend "tbl1" is fixed and "tbl2" is updated (by adding records via data entry by a user). Is there something that can go horribly wrong with this set-up? What happens when 2 users are entering data at the same time?

FYI. one network has XP and another has Vista (I will attempt this on the network with all XP first). Also is is not feasable that users entering data will enter the same record at the same time, so this is not a concern (could happen but it shouldnt)

Thanks in Advance!

access 2002 sp3
windows xp home sp3
two linked databases (customers.mdb and customersdata.mdb)
(customer.mdb is the database that is opened for normal use)

this is not a database that i created. i don't know the development history. it was very likely originally created using access 8 or 9. i'm unfamiliar with access upgrade compatibility issues so i'm mentioning this in case there were ever any (i'm not seeing any problems during normal database use).

if there is any additional information i can provide that will help in helping me please don't hesitate to ask.


my primary need:
there are two linked databases that manage customer addresses and orders (low volume, ~200/year). the database has a few phone number fields that only allow 7-digit numbers. these fields need to be expanded to accommodate 10-digit phone numbers. additionally, i need to prefix all existing phone numbers with a particular area code (i imagine using some type of search and replace routine).


my initial problem (other than the fact that i'm an access newbie) was that i did not realize that there were two linked databases. so i was attempting to modify fields in tables (in customers.mdb) that were linked to another database (customersdata.mdb)--this didn't work very well

once i figured out that there was a second linked database (customersdata.mbd) i attempted to modify the phone number fields and data in that database. i was more successful (in that i could actually modify the phone number field properties), but not understanding the overall database design i was left wondering why my changes to the actual data (in customersdata.mdb) didn't display in the forms that were viewed when customers.mdb was invoked.

so that leads me to my first question, what do i need to learn about the database design that will help me to understand the relationship between the data that i see in customers.mdb and the data in customersdata.mdb?

my second question is how do i perform a proper search and replace on data in an access table? i think i'm mainly stuck on how to use wildcards. the example being, i have a phone number field that i've expanded to 10 characters that is populated with numbers that appear in this format "(___) 123-4567)". my initial attempt was to perform a search and replace on just the area code portion by searching for "(???)" and replacing with "(111)". access complained about not being able to find the given string. so then i attempted to search for "(???) ???-????" and replace with "(111) ???-????". this worked better in that access could find the string, but i was hoping that by specifying wildcards in the replace string that access would leave the existing 7-digit data untouched. no-go, access replaced the 7-digit data with question marks.

i then attempted to copy the data to an external editor, perform the search and replace there, and paste the corrected data back into the table. access didn't appear to like me pasting data back into the table (it issued some type of "primary key" complaint that i did not record). i may have gotten further with the pasting of the data back into the table but i suspended my investigation in order to post to this forum.


i've also seen a seemingly odd error message as i've poked around behind the scenes. if anyone could shed any light on this i'd be very grateful.

when switching from the Design to the Datasheet view for any of the tables in customers.mdb i always see this message (i also see this message if i'm in the Datasheet view for any table in customers.mdb and switch to customersdata.mdb):

Microsoft Visual Basic

Run-time error '2194':

You can't set the PictureData property in Datasheet view.

thanks for any and all help,


ill try to explain what im trying to do

i have a database nothing complicated has 1 main table about 6 others for drop down combo boxes

essentially a flat database but thats all it needs to be however i want to put in on a CD also possible on the internet but i think i can work out how to get it to go on CD etc..

what im struggling with is creating a web page that can search the database via either XML or creating a data access page from within access which create's an html file & get it to connect to the database but i cant create a text box that will search the relevent field
or how to create a Query to search for a sring of info from any field or specific fields ie if i have a film title field, actor, director (the database is my video collection).

so for example this page will have all these fields on it in the design of a form
also it must be able to create new new records as well search the existing database.

hope you follow me !

if not please contact me ill try explain better.


I have returned to this good ole faithful forum in the hope that somone could assist in my technical stumbling block. It has been years since my last visit but upon returning I still see the pro names with massive reply counts. My hat's off to those who are doing this as a profession and still have time for people such as myself.

Having cobbled together the code below from other sources I have managed to get an email on click output to outlook.

The DB has two tables


In the bird detail I'm planning on recording various types of birds and numbers

tblMain_Bird - Main tbl

BirdID - Autonumber
BirdDescription - Text
BirdNumber - Number

The recorded output would end up ebing

Bird Description - Quantity

My question is this, the information below prints into the tblMain_Bird information great but the sub form that records bird description and quantity has continuous forms. How would I go about printing into the email body the tclbird recorded detail?. Is there a way of looping through the sub forma and printing it out to the stText? (or something similar via another var).

This is a little confusing and any help would be greatly appreciated. Essentially this will allow me to record the birds via the main / sub form and easily email the data to my recipient on a weekly basis. I'm sure this example could be used for a multitude of applications so I'm hoping this may help others should a suitable solution be found. If an example file exists in a better format then please do say I'm not proud to put this to one side and take advice from a new avenue.




Private Sub cmdMailTicket_Click()
On Error GoTo Err_cmdMailTicket_Click

Dim stWhere As String '-- Criteria for DLookup
Dim varTo As Variant '-- Address for SendObject
Dim stText As String '-- E-mail text
Dim RecDate As Variant '-- Rec date for e-mail text
Dim stSubject As String '-- Subject line of e-mail
Dim stTicketID As String '-- The ticket ID from form
Dim stWho As String '-- Reference to tblUsers
Dim strSQL As String '-- Create SQL update statement
Dim errLoop As Error

'-- Combo of names to assign ticket to
stWho = Me.cboAssignee
stWhere = "tblMain_Bird.strUserID = " & "'" & stWho & "'"
'-- Looks up email address from TblUsers
varTo = DLookup("[strEMail]", "tblMain_Bird", stWhere)

stSubject = "Bird Survey"

stTicketID = "123456"

RecDate = Me.txtDateReceived
'-- Helpdesk employee who assigns ticket
'strHelpDesk = Me.cboReceivedBy.Column(1)

stText = "Here are the bird findings for this month." & Chr$(13) & Chr$(13) & _
"The total number of birds spotted this month is " & stTicketID & Chr$(13) & Chr$(13) & _
"Best Regards."

'Write the e-mail content for sending to assignee
DoCmd.SendObject , , acFormatTXT, varTo, , , stSubject, stText, -1

Exit Sub


' Notify user of any errors that result from
' executing the query.
If DBEngine.Errors.Count > 0 Then
For Each errLoop In DBEngine.Errors
MsgBox "Error number: " & errLoop.Number & vbCr & _
Next errLoop
End If

Resume Next

Exit Sub

MsgBox Err.Description
Resume Exit_cmdMailTicket_Click

End Sub

Not finding an answer? Try a Google search.