Event from one form that opens another form: How do I Open to the right record?

[Leopardfist]

I have code for a button click event. Essentially I have a main table form that I use to enter customer data and info. I also have a button that I use to add that customer to a separate table that I use for special Customers. My code copies all the data I need copied to my other table, so I wont waste space here typing my code that actually copies the data to the new table. What I will list are the two commands I am using after that code, to close the current form (the one with the button) and open the form for entering special customer information (the form to edit data on my other table). It works flawlessly, but when it opens the other form, it opens to the first record. I want to know how to make it open to the record which I was viewing when I clicked the button to copy the data.

DoCmd.OpenForm "Special Customer Data Entry", , , Number = Me.Number
DoCmd.Close acForm, "Customer Data"

 

[Len Boorman]

being honest about it having two tables for customers is heading for big problems.

Have a field that denotes Special Customer if you want to do that but a separate table will lead to many problems.

If you want to add additional data for a relative few special customers then have a special data table. All you need here is the Customer ID plus the special data on a 1:1 relationship.

Examine carefully what you are doing.

Copy all the data across for special customers...duplicated data...NoNo

Check your normalisation

Len

 

[KeithG]

I agree Special Customer should just be another atribute/field of the customer entity not another entity itself.

 

[Leopardfist]

Guys, I was using Customer and Special Customer as examples, I figured it would be easier to understand with that example, rather than me explaining my two tables here.

My Database in reality is in a Prison Setting, and is used in a Special Housing Unit. When an Inmate enters we have to enter a LOT of data (like 60 fields in the table). Now when an Inmate gets released out of Special Housing Unit, back to the general population, we simply delete that record. I however have it set up where when this happens (Delete Record Event) it copies all that inmates data to a different table named History.

All of that works fine. What I am doing now, is simplifying the process of entering the data when an Inmate comes into the SHU. If they have been there, they will have a record in the history table. I added a button to the form that is used to browse the history table, titled ADMIT, so that they just browse to that inmates history record, click ADMIT and it copies all the data it needs to the main table. I do NOT copy all data, I have coded in which fields to copy over, as many are only related to that specific instance of admission.

Now, having explained all that, it works flawlessly, when they click ADMIT it creates a new record in the main table, adds in all the data required, closes my browse history form and opens the "Enter Inmate Data" Form. The ONLY problem is thatwhen the form opens, it is set on the 1st record. I want to know how to make it go to the record for the inmate we just copied over.

Now, the biggest problem is this database interface that staff use is simply a switchboard. They have no clue about how to actually run a database, they only know that they enter data in this area, and it prints reports. It would be EASY to do if the interface was an actual VB.NET Application. I tried using variables in my event handler, which erroed out.

The code I tried to use is:
Dim Record as String = Me.Number ;(PK)

That errored out, and I can only assume it is because that syntax is not supported in the access environment.

As you can see in my original post, I open the new form first, so I could reference the old form to get the values, hence the query expression Number = Me.Number. Then after the new form opens, the old one closes with the second line.

I read about the gotorecord method, but it only shows like next/previous/first/last. It doesnt show an example of how to go to a specific record utilizing the value to look for, as in a query.

Keep in mind, the record number of one table will NOT match the record number of the other. Nor will the PK's match. That is why I use Number as th reference, as it can NOT be duplicated in the main table, and is the best value to use to find the record.

 

[Leopardfist]

being honest about it having two tables for customers is heading for big problems.

Have a field that denotes Special Customer if you want to do that but a separate table will lead to many problems.

If you want to add additional data for a relative few special customers then have a special data table. All you need here is the Customer ID plus the special data on a 1:1 relationship.

Examine carefully what you are doing.

Copy all the data across for special customers...duplicated data...NoNo

Check your normalisation

Len

Even under the assumption that I use the same table, the necessity here is that one form lists normal customers/inmates and a second form lists the special/history form. So the root problem remains the same, when I copy the special/history back into the normal table, I want to swap to the other form to finish adding the data that is required to describe why the inmate is being admitted, or other data which is not related to the history data but only related to this new record. My code works fine, it opens the other form, then closes the history form, but I cant figure out how to have it go to the record of the guy I just re-admitted.

For simplicity I will list all of my code for the Button Click Event below.

Private Sub Admit_Button_Click()
    Dim dbs As DAO.Database
    Dim admit As DAO.Recordset
    Set dbs = CurrentDb
    Set admit = dbs.OpenRecordset("Special Housing Unit Inmate Data")
    With admit
        .AddNew
            ![Date of Arrival] = Date
            ![Date Inmate Placed in Cell] = Date
            ![Time of Arrival] = Time
            !FirstName = Me.FirstName
            !LastName = Me.LastName
            !Number = Me.Number
            !Custody = Me.Custody
            !Unit = Me.Unit
            !Race = Me.Race
            !Meal = Me.Meal
            !Affiliation = Me.Affiliation
            !Separtees = Me.Separtees
            ![Pertinent Information] = Me.[Pertinent Information]
            ![Psychology Watch] = Me.[Psychology Watch]
            ![Two-Hour Watch] = Me.[Two-Hour Watch]
            ![Assault History] = Me.[Assault History]
            ![Drug History] = Me.[Drug History]
            ![Hard Restraint only] = Me.[Hard Restraint only]
            ![SIS Notification] = Me.[SIS Notification]
            ![Suicide Risk] = Me.[Suicide Risk]
            !Notes = Me.Notes
            .Update
    End With
    DoCmd.OpenForm "Special Housing Unit Inmate Data Entry", , , Number = Me.Number
    DoCmd.Close acForm, "History"
End Sub

 

[grnzbra]

Well, assuming that Me.Number is unique among prisoners and doesn't get used agaiin or changed (if numbers are re-used, you just need to add dates) could you select the record based on the Me.Number value?

Unless the SHU records are totally separate from the general population records and must stand alone, (ie, SHU database cannot see the general population records) there is a lot of stuff (first name, last name, race and perhaps others) that shouldn't be in the second table.

 

[Leopardfist]

YES! The number is Unique on the current table, and is what I tried to use to filter the records so only the new record would show, by using the command below.

DoCmd.OpenForm "Special Customer Data Entry", , , Number = Me.Number

But it does not work, it does not filter out any records at all. So essentially I need to remove the "Number = Me.Number" part, and add a new command that will make the form go to the record with that specific nuumber. To do this I will need to use a variable while my history form is open, in order to store the number, then after I close the hisotry form use the variable to find the form I need. At least I am assuming that. I tried to use a variable and it gave me an error, as mentioned before, so if I did it wrong can you tell me how to do it right? I do not know cap about VB prior to VB.Net, and don't know a whole lot about VB.Net, so please be specific.

THANKS!

 

[grnzbra]

Place this code in the procedure that calls the second form. It will (should) modify the query driving the second form.

dim qdf as querydef
dim strsql as string
dim strwhr as string

set qdf = currentdb.qdf("NameOfQueryDrivingSecondForm")

strsql = SQL statement of "NameOfQueryDrivingSecondForm" 'without where clause.

strwhr = "Where Number = " & Me.Number 'if Number is alpha, ...= '" & Me.Number & "'"

strsql = strsql & " " & strwhr

qdf.SQL = strsql

 

[Leopardfist]

I actually just got it to work, by making the filter a variable rather than trying to use the Me.Number.

Dim criteria As String
criteria = "[Number]=" & "'" & Me![Number] & "'"

and changing my command at the end to:
DoCmd.OpenForm "Special Housing Unit Inmate Data Entry", , , criteria

That filters out all other records, opening only the record the user Admits. Once completed they simply close the form, and re-open it to see all records.

Thanks for all the assistance though!

 

[lightray]

Sorry learner driver here!
Just so I understand the answer to this thread, Me![Number] was a actually a text field,
and the original code was saying, test it as an integer yes?
lightray