'Go To' code

[AndyCabbages]

Hey, I am currently working on a database which was made by someone who is no longer contactable to help and my VB is not great.

Basically within one of the forms there is a 'GoTo' button, which when clicked prompts you to enter a 'Record Number' which is not as simple as being an autonumber which counts from 1 depending on the number of records. For some reason when you enter a 'Record Number' which is definitely valid it does not direct you to the coressponding record.

Below is the code for the comand relating to the 'GoTo' button:


Private Sub Command69_Click()
On Error GoTo Err_Command69_Click

Dim record As Integer
record = InputBox("Please enter the record number which you would like to go to?", "Goto Record")

Dim dbsThisDatabase As Database
Dim TableRecords As DAO.Recordset
Set dbsThisDatabase = CurrentDb()
strSQL = "SELECT Order.Record_Number, Order.Company_ID, Customers.*, Order.Contact_Name, Order.Contact_Number, Order.Order_No, Order.Tax_Point_Date, Order.INV, Order.Site, Order.Complete, Order.Discount, Order.PreparedBy FROM Customers INNER JOIN [Order] ON (Customers.Company_ID=Order.Company_ID) AND (Customers.Company_ID=Order.Company_ID) WHERE (((Order.Complete)=False)) ORDER BY Order.Record_Number"
Set TableRecords = dbsThisDatabase.OpenRecordset(strSQL)
TableRecords.MoveLast
TableRecords.MoveFirst
moveRecords = 1
isFound = False
Do While record <> TableRecords!Record_Number And moveRecords < TableRecords.RecordCount
TableRecords.MoveNext
moveRecords = moveRecords + 1
If record = TableRecords!Record_Number Then
isFound = True
End If
Loop

If isFound = True Then
DoCmd.GoToRecord , , acGoTo, moveRecords
RefreshContracts
Else
MsgBox "The record that you require cannot be found. Either its in Old contracts or doesn't exist."
End If

Err_Command69_Click:
End


Hopefully someone with a bit better understanding of VB will be able to make some sense of it


Andy

 

[pbaldy]

In a word, "bleh". That's a very inefficient method. Since it appears the code is on a bound form, I would try the combo box wizard, choosing the third option, "Find a record...". It would be much simpler.

 

[AndyCabbages]

Haha, yeah quite a lot of this database is badly designed and coded and I'm stuck with cleaning up the mess, despite my complete lack of experience with VB.

I tried what you duggested and created a combo box and chose the 3rd option in the wizzard (Fine a record on my form based on the vlue I selected in my combo box). I then slect the 'Record_Number' field as the one to be searched against and get the following error:

Syntax error in query expression '[SELECT [Order]].[Record_Number]'.

 

[pbaldy]

Can you post the code, and what the selected value would look like (number, text)?

 

[AndyCabbages]

What code do you mean?

And is this what you mean when you ask about the selected value?:

'Record_Number' is actually of type 'AutoNumber' (I didnt realise this origionally) which for some reason is typically a 5 digit number in the 30,000ish range (starting at 31,069) with the following profile:

Field Size: Long Integer, New Value: Increment, Indexed: Yes (No Duplicates)

 

[pbaldy]

I was looking for the code behind that combo. Based on what you just posted, I'd expect:

    Dim rs As Object

    Set rs = Me.Recordset.Clone
    rs.FindFirst "[Record_Number] = " & Me.ComboName
    Me.Bookmark = rs.Bookmark

If it looks like that and still isn't working, what are the rowsource, the bound column and column count properties of the combo? Or can you post the db?

 

[SOS]

Paul -

Could it be one of those "Lookup field" problems where there are lookups at table level?

 

[pbaldy]

I suppose it could; I never use the blasted things, so I'm not familiar with all the errors that come from using them.

 

[AndyCabbages]

I cant give you the code for the combo box since the error is occuring before the wizzard even finishes so I cant actually get a finished Combobox

 

[pbaldy]

Okay, try putting a combo on the form that just lists the record numbers from the table, and then create an after update event and paste the code above into it.

 

[gemma-the-husky]

One observation

RIGHT AT THE TOP

Dim record As Integer

definitely an error - you cant go above 32767 with this. This will give you an error I think, if you go above this number.

you need

Dim record As LONG

This may fix your immediate issue

---------------------

Out of interest, what your code appears to do, is loop through all the records until it finds one that matches the record you have searched.
This is inefficient. It could easily just do a simple search/find operation to jump directly to the item.

But as others have pointed out, this is not a very helpful technique anyway., since you have to know the record number.

Its far nicer to present some meaningful information LINKED to the record number (say a Company Name) - you pick the meaningful data, access knows this corresponds to the selected Id, and uses the record id, in the same way as if you have entered thatr number.

 

[AndyCabbages]

Gemma, you are an absolute life saver!

I looked at that code for ages thinking it was some massively complicated error somewhere deep within the code and turn out it was something as trivial as that, haha.

Thank you very much! The 'GoTo' button now works perfectly as intended