Using Existing File Number to Copy Data

[tdw]

Hi, I have taken over an orders database that someone else created. I have basically backwards engineered it, thereby learning Access and VBA on my own. I have successfully made many improvements to it, but I am stuck on two things.
First thing is, I want to be warned if I have just typed a value into the "address" line of the form that is a duplicate of an already existing value in the "address" column on a different table, but i don't want it to prevent me from entering that duplicate if I want to... just warn me. I have tried several things, but the one thing I can't seem to get around is that if the address is new, i.e. is not already duplicated in the other table, I still get a query result of a blank form. what I want is for it to do nothing at all if the address I just typed is new. The second thing is I want to create a form that lets me type our file number associated with an order, and upon 'lose focus' automatically fill in several other values in the form, and then have that info saved into it's own table. the reason for this is so that I have a dedicated table for orders that are being assigned to a crew to be taken care of today, without needing to re-type all the data.
I have tried several of the solutions to similiar problems posted on here and so far no luck. It either leaves all the fields blank, or gives me a parameters error.

[Rabbit]

Can you post the code you're using? It'll give us something to work off of.

[MMcCarthy]

Hi, I have taken over an orders database that someone else created. I have basically backwards engineered it, thereby learning Access and VBA on my own. I have successfully made many improvements to it, but I am stuck on two things.
First thing is, I want to be warned if I have just typed a value into the "address" line of the form that is a duplicate of an already existing value in the "address" column on a different table, but i don't want it to prevent me from entering that duplicate if I want to... just warn me. I have tried several things, but the one thing I can't seem to get around is that if the address is new, i.e. is not already duplicated in the other table, I still get a query result of a blank form. what I want is for it to do nothing at all if the address I just typed is new.

Create an after update event on the [address] textbox as follows:

Private Sub address_AfterUpdate()
   If Not IsNull(DLookup("*", "OtherTableName", "[address]='" & Me![address] & "'") Then
      Msgbox "Address already in table ...etc.", vbOkOnly
   End If
End Sub
 

The second thing is I want to create a form that lets me type our file number associated with an order, and upon 'lose focus' automatically fill in several other values in the form, and then have that info saved into it's own table. the reason for this is so that I have a dedicated table for orders that are being assigned to a crew to be taken care of today, without needing to re-type all the data.
I have tried several of the solutions to similiar problems posted on here and so far no luck. It either leaves all the fields blank, or gives me a parameters error.

I will need a clearer explanation of what you are trying to do here and what table(s) the current form is based on and what table you wish the data saved to. As the previous example this can be programmed in a triggered event but I will need to know more about what's going on to figure out when and how to trigger it.

Mary

[tdw]

Here is the Code for the form for creating new orders. I tried the code suggested previously, and also tried it as a Lost Focus event, as you can see here. It doesn't respond. For the heck of it I threw in an 'Else' to see if it even gives me that message box. Nothing, no response. This Add New Order form is linked/associated (not sure the right term) with a table called SC_NEW. Once an order is typed up, there is a button to click that enters it into an open orders table called SC_OPEN. Then, after we have completed and billed the job we click a button to archive it (close the order) in the table SC_ARCH. The original programmer only had the SC_OPEN and SC_ARCH tables. The problem with that was that if we closed an order (i.e. due to cancellation) but then wanted to re-open it (i.e. customer changed their mind and said go ahead with it) we couldn't move the record back into the Open Orders table because of the autonumber field that was being used to generate our file numbers (through a complex and, to me kind of bizarre, series of queries and formulas). An example of a file number: 2007-0987 (where the year ordered is 2007 and it's the 987th order for the year). So I added the SC_NEW table, which now has the autonumber field to generate the file number, then on button click moves the date to the SC_OPEN table which does not have autonumber field. Now we can move a record back into it from the SC_ARCH table with no problem. Now I want to add a feature that warns when we are about to type in an order with the same property address that we already have an open order for, because sometimes our clients fax us the same request twice and we don't catch it. Then we end up with two orders for the same job and that can get messy. But I don't want it to prevent me from reusing an address, in case we are entering a seperate order for a different job at the same address. So in a New Order form, linked to the SC_NEW table, I need the address field to recognize a duplicate address that is located in the SC_OPEN table. Since my original post had two questions, I will go into more detail on the other question in a seperate reply, so that they can be getting answered seperately. Thanks so much!

Option Compare Database
Option Explicit
'   Path Name code Variable Definitions
    Private stFileNumber As String
    Private stFileName As String
    Private stPathName As String
    Private stPFName As String
Private cusnamers As Recordset
Private dbs As Database
 
Private Sub ADDRESS_LostFocus()
 
DoCmd.SetWarnings True
    If Not IsNull(DLookup("*", "SC_OPEN", "[ADDRESS]='" & Me.ADDRESS & "'")) Then
        MsgBox "There is already an Open Order with this Address", vbOKOnly
    Else: MsgBox "this isn't working"
    End If
 
End Sub
 
Private Sub Create_Order_File_Structure_Click()
On Error GoTo Err_Create_Order_File_Structure_Click
 
'   Yes No Box
    Dim Msg, Style, Title, Response
    Msg = "Are you sure you want to Create this new Open Order?"    ' Define message.
    Style = vbYesNo   ' Define buttons.
    Title = "Create Order"    ' Define title.
    Response = MsgBox(Msg, Style, Title)
If Response = vbYes Then    ' User chose Yes
 
    RunCommand acCmdSaveRecord
 
    If Me![REC_ID] <> 0 And Me![FILE_NO] = "" Then
    DoCmd.SetWarnings False
        If Me![REC_ID] - 10493 < 10 Then ' Numeric Value 10493 is last value of rec_id upon year change for file_id renum
        DoCmd.OpenQuery "FILE_NO_Update_New_000", acNormal, acEdit
        GoTo Confirm_File_No_Creation
        End If
 
        If Me![REC_ID] - 10493 < 100 Then
        DoCmd.OpenQuery "FILE_NO_Update_New_00", acNormal, acEdit
        GoTo Confirm_File_No_Creation
        End If
 
        If Me![REC_ID] - 10493 < 1000 Then
        DoCmd.OpenQuery "FILE_NO_Update_New_0", acNormal, acEdit
        GoTo Confirm_File_No_Creation
        End If
 
    DoCmd.OpenQuery "FILE_NO_Update_New", acNormal, acEdit
 
Confirm_File_No_Creation:
    DoCmd.SetWarnings True
    MsgBox ("The File Number Has Been Created For The Order")
 
    End If
 
    stFileNumber = [FILE_NO]
    stPathName = "O:\" & [FILE_NO]
    stFileName = [FILE_NO] & ".rtf"
    stPFName = stPathName & "\" & stFileName
 
    If Dir$(stPathName, vbDirectory) <> "" Then  ' Does Path Already Exist?
        MsgBox ("The directory " & stPathName & " Already exits")
        GoTo Exit_Create_Order_File_Structure_Click
    Else
        MkDir Path:=(stPathName)   '  Create Directory Structure
    End If
 
    Application.FileSearch.RefreshScopes  '  Refresh Directory Structure
'   Create Report File
    DoCmd.OutputTo acOutputReport, "File Creation", acFormatRTF, stPFName
    MsgBox ("The " & stPathName & " folder and the file " & stFileName & " have been created")
 
Else    ' User chose No.
    GoTo Exit_Create_Order_File_Structure_Click
End If
 
'   Query Name variable definitions
    Dim stQName As String
    Dim stQName1 As String
    stQName = "APPEND NEW RECORD TO OPEN" 'Named Database Query
    stQName1 = "Delete From New"  'Named Database Query
 
    DoCmd.SetWarnings False
    DoCmd.OpenQuery stQName, acNormal, acEdit   'Run Query
    DoCmd.SetWarnings True
 
'   Yes No Box
    Msg = "Do you want to Print " & stFileNumber & "?"    ' Define message.
    Style = vbYesNo   ' Define buttons.
    Title = "Print New Order"    ' Define title.
    Response = MsgBox(Msg, Style, Title)
If Response = vbYes Then    ' User chose Yes
    DoCmd.RunMacro ("Print New Order") '   Print New Order
End If
 
    DoCmd.SetWarnings False
    DoCmd.OpenQuery stQName1, acNormal, acEdit  'Run Query
    DoCmd.Requery   'Refresh Form
    DoCmd.SetWarnings True
Exit_Create_Order_File_Structure_Click:
    Exit Sub
 
Err_Create_Order_File_Structure_Click:
    MsgBox Err.Description
    Resume Exit_Create_Order_File_Structure_Click
End Sub
 
Private Sub Form_Load()
DoCmd.SetWarnings False
DoCmd.OpenQuery "customer_distinct_create_table", acNormal, acEdit
DoCmd.SetWarnings True
 
Set dbs = CurrentDb()
Set cusnamers = dbs.OpenRecordset("cust", dbOpenTable)
 
DoCmd.SetWarnings False
DoCmd.OpenQuery "subdivision_distinct_create_table", acNormal, acEdit
DoCmd.SetWarnings True
 
End Sub
 
Private Sub ORDER_KeyUp(KeyCode As Integer, Shift As Integer)
 
 
Dim nl As Integer 'number of characters typed in order field
Dim i As Integer
nl = Len(ORDER.Text)
If nl >= 5 Then
        cusnamers.MoveFirst
        For i = 1 To cusnamers.RecordCount
        If UCase(Left(cusnamers("cusname"), nl)) = UCase(ORDER.Text) Then
            ORDER.Text = cusnamers("cusname")
            Exit For
        Else
            cusnamers.MoveNext
        End If
        Next
 End If
End Sub
 

[tdw]

Ok, my other issue. When we send a job out to the field, currently the field coordinator has to hand write the file number, address, etc. down. The reason is that if we get a phone call about the status of an order, and if we can't find the folder for the order in our office, we need to know if it's lost, never been created, or out in field being worked on right now. so the field coordinator writes down all the jobs every morning and what crew he sent them out with. I am trying to give him a way to get on our orders database, type into a form what crew it's going with, type in the file number of the job, and have it fill in the rest of the info, like the address, subdivision, lot number, etc. This would expedite the process of getting the crews out in the field in the morning. Then I want that data stored so that we can look up what job went out with whom on any given day. So I need the form to lookup info from the SC_OPEN table based on the file number he enters into the form, then store it all on a separate table that I would call IN_FIELD or some such thing. Then I can create a query to look up all jobs that went in the field on a certain day and get a report of them and what crew they went with. One reason I think I should do all this with it's own table rather than adding fields to the SC_OPEN table is that we would want to be able to enter criteria for a particular date, and not have it need to search across two tables for the data (some jobs that went in the field may not be closed yet, or some may be, so some would be in SC_OPEN and some in SC_ARCH). Also that would allow him to accidentally screw up data on his In Field report without screwing up our data in the SC_OPEN or SC_ARCH tables.

[MMcCarthy]

Open the form in design view. Go to textbox [address] and open properties window. Under the event tab make sure [Event Procedure] appears against the Lost Focus or After Update event as appropriate. I would leave it as After Update BTW.

[tdw]

Open the form in design view. Go to textbox [address] and open properties window. Under the event tab make sure [Event Procedure] appears against the Lost Focus or After Update event as appropriate. I would leave it as After Update BTW.

Yeah, makes sense to have it as After Update. It's just that I tried that and when it didn't work I thought I'd try it on Lost Focus. Ok, I've moved to back to After Update, but still nothing. It does say [Event Procedure] and it does go to the right code when I click the "..." button that appears. When exactly shoud the message box appear? I tried tabbing out of the address box after typing, nothing happened, so I clicked to the the next record and got no response along the way. I know that I am typing an existing address. By the way, if it makes any difference, I am using Access 2003, and I believe that the database was created using 2002.

[MMcCarthy]

Yeah, makes sense to have it as After Update. It's just that I tried that and when it didn't work I thought I'd try it on Lost Focus. Ok, I've moved to back to After Update, but still nothing. It does say [Event Procedure] and it does go to the right code when I click the "..." button that appears. When exactly shoud the message box appear? I tried tabbing out of the address box after typing, nothing happened, so I clicked to the the next record and got no response along the way. I know that I am typing an existing address. By the way, if it makes any difference, I am using Access 2003, and I believe that the database was created using 2002.

As a test try compiling the code in 2003 and see if any errors appear.

[MMcCarthy]

Try this code ...

Private Sub ADDRESS_AfterUpdate()
 
   If DLookup("*", "SC_OPEN", "[ADDRESS]='" & Me!ADDRESS & "'") <> 0 Then
      MsgBox "There is already an Open Order with this Address", vbOKOnly
   Else
      MsgBox "this isn't working"
   End If
 
End Sub

[tdw]

Try this code ...

Private Sub ADDRESS_AfterUpdate()
 
   If DLookup("*", "SC_OPEN", "[ADDRESS]='" & Me!ADDRESS & "'") <> 0 Then
      MsgBox "There is already an Open Order with this Address", vbOKOnly
   Else
      MsgBox "this isn't working"
   End If
 
End Sub

Ok, tried it. Still nothing, no message box. When you say to try compiling the code, do you mean the 'compile orders' in the 'debug' menu? If so, it found no errors in the code for this form, just in the code for the other form I'm trying to create that's unrelated to this one.

I was just reading up on using 'dlookup' and might have found what I need for my other question, but no insight into this one

[MMcCarthy]

Sorry my fault, try this ...

Private Sub ADDRESS_AfterUpdate()
 
   If DCount("*", "SC_OPEN", "[ADDRESS]='" & Me!ADDRESS & "'") <> 0 Then
      MsgBox "There is already an Open Order with this Address", vbOKOnly
   Else
      MsgBox "this isn't working"
   End If
 
End Sub

Mary

[tdw]

YES!! It worked. Thank you so much! Now I will add a yes/no message asking the user if they would like to view the existing order with that address, program it to do so, and I'm all set! That part should be easy.

I will try a couple things on the other question I had, and I'll start a new thread restating that question if I still have no luck.

Sorry my fault, try this ...

Private Sub ADDRESS_AfterUpdate()
 
   If DCount("*", "SC_OPEN", "[ADDRESS]='" & Me!ADDRESS & "'") <> 0 Then
      MsgBox "There is already an Open Order with this Address", vbOKOnly
   Else
      MsgBox "this isn't working"
   End If
 
End Sub

Mary

[MMcCarthy]

YES!! It worked. Thank you so much! Now I will add a yes/no message asking the user if they would like to view the existing order with that address, program it to do so, and I'm all set! That part should be easy.

I will try a couple things on the other question I had, and I'll start a new thread restating that question if I still have no luck.

You're welcome.