Find specific Primary Key record number?

[Fazered]

I now have a new and simple (ish) problem that needs solving, however, i think it involves a fairly complex solution.

I have a form as shown below.


That should describe most of the basics of the form.
Here is the problem:

You can use the navigation buttons to successfully go through all customers and view their orders in the subform. You can also successfully type a customer number in the 'Customer No.:' textbox and click find to view that customers orders.

The 'Customer No.:' textbos is unbounded and simply read when the find button is pressed. The textbox above that (which will eventually be made invisible) is bound to the column 'Customer No.' in the 'Customer' Table through the form.

The reason for the textbox with no label is to only move to customer numbers that exist, for example 2,3,7,10,27, etc rather than 1,2,3,4,5, etc.

The customer No.: Textbox is the one that does all the searching and is passed values from the textbox above when you navigate through the customer table.

The problem is when I search for a customer number i get the correct results, but i cannot figure out how to more the current record of the customer table to the searched customer number.
(So i'd start with the 2 customer number textboxes with both 2's in them, i'dthen search for customer no. 7, and get the results but the un-labeled textbox also needs to change to 7.

>>I apologise now if this is extremely hard to understand, its extremely hard to show what i mean unless i videoed the problem so you can see what happens.


I have tried the following code in the 'OnClick' event of the 'Find' button:

Private Sub FindAccount_Click()

    Me.ViewAccount_Subform.Requery
    
    CustNo = Me.ViewCustomerNumber

    DoCmd.FindRecord CustNo, acEntire, True, acSearchAll, , , True
    DoCmd.GoToRecord acDataForm, "ViewAccount", acGoTo, CurrentRow
    
End Sub

Im trying to find the record number of the current Customer No. in the Customer table and then GoTo that record, but i cannot get the record number of the customer number in the customer table.

Any ideas? (Assuming you understand the problem, lol)

 

[CyberLynx]

Me.Filter = "[RecordID]=" & DLookUp("[RecordID]", "Customers", CustNo=" & Me.ViewCustomerNumber)
Me.FilterOn = True

.

 

[Fazered]

>>>Sorry about that, double post<<<

 

[Fazered]

Thank you for the quick response. I have tried your code, but don't understand where you got "[RecordID]" from. What exactly is its purpose?

What i really need to do is to find the record number of a given primary key for a tuple in a table. This will allow me to set the form current record to the one that i've found.

So does anyone know how to find the record number of a tuple?
Also, does anyone know how to set the form to go to the record number?

Here is an example of what i mean by record ID's:

 

[Rabbie]

Access tables have no absolute record number. RecordId in this case is the PK of that table. You can order a recordset in anyway you want.

 

[gemma-the-husky]

the oridinal position of a record in a table/query cannot be relied upon

ie if in the navigation box at the bottom of the form you enter 20, return you will move to the 20th record - if you resort the records, then obviously the 20th record will not be the same.

therefore the only safe way is to identify the primary key of the record (ie the recordid referred to above) and move to that item.

is that what you mean?

 

[Fazered]

Damn! Hmm, that poses a small problem.
I shall have to take a different approach to this then. Thanks for everyones help.

 

[Fazered]

the oridinal position of a record in a therefore the only safe way is to identify the primary key of the record (ie the recordid referred to above) and move to that item.

is that what you mean?

Thinking about it, yes that would work. Although im not sure how to do it.

So here my new question.

If i have a form which is bound to the Customer table (Shown above), and i have a 'Customer Number' Textbox which is bound to 'Cardinalis Customer Number' in the Customer Table.

If the current value is 3 (for example), how do i changed it to (10)?
Bearing in mind that the primary key's go 2,3,4,7,8,9,10,27.

 

[gemma-the-husky]

generally you probably wont know the primary key of the record you want, since it is most likely an autonumber. You will know the name, so you can click in the name field, and use search (binoculars icon) to find the correct record.

you can program this search facility in a variety of ways if you dont want to use the binoculars (which is tricky for users until they get used to it)

 

[Fazered]

In this case it will be the primary key or name (name is yet to be added) that will be searched for.
I dont suppose you know how to find the a record with a certain primary key or name and then go to it, do you?

 

[gemma-the-husky]

see above - use the binoculars (or edit/find)

 

[CyberLynx]

I apologize for the lack of explanation within my last post. Now that I have seen your Table (image) it should look like this but...you will still need to ensure that the Table Field names are correct since all I can see within the image posted are the Captions for those Table Fields.

I'm trying to find the record number of the current Customer No. in the Customer table and then GoTo that record, but i cannot get the record number of the customer number in the customer table.

Let's clear something up here. What is the name used in the Customer Table for the AutoNumber Field (if there is a AutoNumber field)? Is it CustNo or is it Cardinalis Customer Number? What Field is in fact holding the record number? Without knowing for certain, I would have to assume or guess what it is.

Lets' try this again....I'm going to again assume that the name of the Table field which is holding the Record Number is CustNo. Within the OnClick event of your Form's Find command button:

Me.Filter = IIf(Nz(DLookup("[[COLOR="Red"][I]CustNo[/I][/COLOR]]", "[COLOR="Red"][I]Customers[/I][/COLOR]", "[[COLOR="Red"][I]CustNo[/I][/COLOR]]=" & _
                Nz(Me.ViewCustomerNumber, 0)), 0) > 0, "[[COLOR="Red"][I]CustNo[/I][/COLOR]]=" & _
                Me.ViewCustomerNumber, "[[COLOR="Red"][I]CustNo[/I][/COLOR]] > 0")
Me.FilterOn = True

You will need to ensure that the names indicated by Red Italic within the code above contain the correct names.

Here is what is happening with this code. It will ultimately Filter in the record(s) required based on the Customer number you enter within the search Text Box located at the very top of your Form (as seen in your provided image). Well, at least that is the goal here.

We are using the IIF() Function here for one simple purpose only. To allow the display of all Customer records if nothing was supplied within the search Text Box and if the Find button is selected. Otherwise it will Filter in all records from the Customer Table related to the supplied number.

The IIF() function works in this fashion:

IIf(expr, truepart, falsepart)

If the expression is True then the code within the truepart is run otherwise the code in the falsepart is run.

You will also notice the Nz() Function is also used within the code. The purpose of this Function is to deal with NULL values. If a returned value from the expression contained within the Nz() Function is NULL then the Nz() Function will return the Value specified within the last parameter of the Function which in the case of this code, is 0 (zero). Both the DLookUp() function and the Form Field ViewCustomerNumber are used as expressions within the two Nz() Functions contained in code.

In other words, if the DLookup() Function returns NULL because it can not locate the required record or records, the Nz() function ensures that 0 is returned instead of NULL. We use this returned value as the Condition for our IIF() Function. So, if DLookup() can find the Customer number within Table then we place [CustNo] = n into the Form's Filter property where n is the supplied data from the ViewCustomerNumber Text Box on Form. The record or records with the supplied Customer Number should be displayed.

You will also notice that the Nz() Function is used to ensure NULL is not passed to the DLookup() Function from the Form Field ViewCustomerNumber. This helps certify our condition for the IFF() function where as it ensures that if nothing is supplied within the ViewCustomerNumber Text Box, we provide 0. The Nz() Function will do this for us. This would ultimately provide a False condition for the IFF() Function since there would be no record number which contains 0 (zero), so, the Code [CodeNo] > 0 is placed within the Form's Filter property which would display all records.

Pffft....And you thought your initial post was confusing.

.

 

[Fazered]

That is very similair to what i wish to do, and will definatley come in handy with another part of the database. But before i try and explain in a little more details what i wish to accomplish, i shall clarify a few things.

The Autonumber for the Customers Table is: CardinalisCustomerNumber (The spaces have recently been removed)
There is no RecordID field. The only unique identifier for each record/tuple is the Autonumber above.

[There have been a few changes that have been made to the form and the way its going to work, so i'll re-explain a few things]




The forms control source is the 'Customer' table (part of which i showed earlier).
The navigation buttons on the form, move through the records (First, Back, Next, Last).
The 'Customer Number', 'Customer Name', 'Date Added' and the 'Stop Account' tickbox, all show details from the 'Customer' table as you flick through the record with the navigation buttons.

The 'Customer Number:' textbox in the top right is now where you type a customer number and click find to go to that customer record in the 'Customer' table, the textbox is then cleared for the next search.

Everything thus far works fine and dandy.

The problem comes when, you have just searched for a customer and wish to move to the next or last record.

For example, When you open up the form, it open on 'Customer Number' 2, you click the next button and it goes to 'Customer Number' 3. Now you wish to search for a customer number, so you enter a customer number, 8, into the 'Find' textbox and click find. The forms details then change to customer 8's details.

Heres where the problem is. The textboxes show the details for Customer 8, but according to the navigation buttons on the form, you are still on Customer 3. So when you click the next button it goes from Customer 8 to customer 4.

So, there's my problem. How do i make it so that after searching for a 'Customer Number' in the top right, the navigation buttons will go the next or last customer in the table in relation to the customer number that was just entered?

 

[CyberLynx]

The forms control source is the 'Customer' table (part of which i showed earlier).

A Form does not have a Control Source Property, it does however have a Record Source property. I can only imagine this is what you mean.

The text boxes show the details for Customer 8, but according to the navigation buttons on the form, you are still on Customer 3. So when you click the next button it goes from Customer 8 to customer 4.

Since I don't see a Text Box which indicates the absolute record number of the current Recordset within the posted Form image, I must assume that what you are talking about is "record naivigation wise" rather than "aboslute record number wise" (visual).

I will go by what I see so far. Your Main Form is bound to the Table Customer by way of the Record Source property. Your SubForms are linked to the Main Form by way of:

Link Child Fields is CardinalisCustomerNumber
Link Master Fields is CardinalisCustomerNumber

When you search for a specific Record, you are searching for the literal Record Number as it is saved in Table within the CardinalisCustomerNumber Field. Upon selection of the Find button, the record matching the number entered into the Find Customer Number Text Box and the number located in the CardinalisCustomerNumber Table Field is displayed (populates) the Form.

Now....if you select the Next navigation button, the Form should populate with the CardinalisCustomerNumber of 9. If instead, the Previous navigation button was selected then record number 7 populates the Form.

Please correct me if I am wrong with the above scenario.

If this is the case...then Filtering to populate the Form is obviously not the answer since this will create a distinct Recordset based on that Filter. Nor will a specific SELECT query solve this problem. We need to play within the global Recordset of the entire table or the initial Query that creates the population for the Form.

I don't know what code or mechanism you are currently using within the FIND button to populate the Form with the supplied Find Customer Number. This has not yet been disclosed.

What I suggest you use for Code beneath the OnClick event for the FIND button is this:

Private Sub FindButton_Click()
   Dim rst As DAO.Recordset
   Set rst = Me.RecordsetClone
   rst.FindFirst "[CardinalisCustomerNumber]=" & Me.[I][COLOR="Red"]NameOfFindCustomerNumberTextBox[/COLOR][/I] 
   If rst.NoMatch = False Then
      Me.Bookmark = rst.Bookmark
   End If
End Sub

Try that and let me know how it works out.

.

 

[Fazered]

A Form does not have a Control Source Property, it does however have a Record Source property. I can only imagine this is what you mean.

That was indeed what i mean, i must have accidently written the wrong thing.


On another note, that code was EXACTLY what i was looking for. I think thats 3-4 days of confusion and problems solves in only a few lines of code. Thank you.

 

[Fazered]

Actually i do have one more question about that form.

When you reach the last record in the form which in this case is customer number 27, and press the next button again, the form moves to the blank record which would normally be used to input a new record in the table. The record im talking about is (New).

Because of the code i have implemented in the form, i really dont want the user to be able to move past the last record in the form, or a horrible error occurs.

Do you know how to effectively disable the next navigation when it reaches the last record in the table/form?

 

[gemma-the-husky]

you can set a form property to

allow additions = false - that stops the new record showing

 

[Fazered]

Thought i tried that at some point. Perhaps i didn't.... nevermind, Thank you.

 

[CyberLynx]

You are very welcome Fazered...and something to keep in mind.

What is confusion to one, may be the solution to many.

.

 

[Fazered]

Ok, i know this isn't really related to the my previous questions but it saves creating a new thread.

I am trying to declare a public subroutine called 'DebugForm' which will receive a few or large number of variables and then put these into textboxes in a debugging form. (Obviously for the purpose of debugging).

I currently have 24 input variables for this subroutine, but dont want to have to give the subroutine all 24 variables just to display 1 unique variable.

I found that so make a variable optional, you put 'Optional' infront of the variable declaration.

Any suggestions on how to do this with 24 variables (all variants) from a - x.

This is what i have at the moment, but get the error: 'Duplicate delcaration in current scope'.

Public Sub DebugForm(Optional a As Variant, Optional b As Variant, Optional c As Variant, Optional d As Variant, _
                     Optional e As Variant, Optional f As Variant, Optional g As Variant, Optional h As Variant, _
                     Optional i As Variant, Optional j As Variant, Optional h As Variant, Optional l As Variant, _
                     Optional m As Variant, Optional n As Variant, Optional o As Variant, Optional p As Variant, _
                     Optional q As Variant, Optional r As Variant, Optional s As Variant, Optional t As Variant, _
                     Optional u As Variant, Optional v As Variant, Optional w As Variant, Optional x As Variant

Any Ideas?