Jump to record from textbox

[Ciprian]

Hi,

I'm trying to jump to a record from a textbox.

So i have a form [Inserare Contracte] which has the record source the table [tblContracte]. In that form I have 2 textboxes. In the first one [FactNr] i enter the invoice number, then in the second one [ContractNr] i get the Contract that that invoice is linked to. The second textbox has this Control Source Code:

=DLookUp("[cContractsID]";"[q nr ctr dupa factura]";[FactNr])

. [cContractsID] is the field's name.

I think the best way to do the jump to the record is to have an after update event, but i'm not really sure how to code it.

Thanks for the help

 

[vbaInet]

* What is FactNr?
* What is the data type of FactNr?
* Is FactNr also a field in [q nr ctr dupa factura]?

=DLookUp("[cContractsID]";"[q nr ctr dupa factura]";"[FactNr] = " & [FactNr])

Put it directly in the Control Source of the textbox.

 

[Ciprian]

Hi and Happy New Year!

Like i said in the first post, there are 2 Text boxes. The 1st one is named "FactNr". The 2nd is named "ContractNr".

The source of the 2nd one is

=DLookUp("[cContractsID]";"[q nr ctr dupa factura]";[FactNr])

[cContractsID] - text field in a table with stores the Contract ID
[q nr ctr dupa factura] - is a query that returns the Contract ID for an given invoice number, that is introduces in the 1st textbox

So this works fine, I introduce the invoice number in the 1st textbox, press enter and i get the liking contract in the 2nd textbox.

But if i want to go to that Contract i have to copy the name and then search for it in the form.

What I want to be able to do is hit the enter key again and the Form [Inserare Contracte] in which the 2 text boxes are located to automatically jump to that record.

 

[vbaInet]

Did you even try what I gave you in my last post?

 

[Ciprian]

yes i have and it's not doing anything

 

[vbaInet]

I would ask again, what is the data type of FactNr?

 

[Ciprian]

I would ask again, what is the data type of FactNr?

The type is Number.

But FactNr is just used in the aforementioned query as criteria.

 

[vbaInet]

Use this:

dim rs as dao.recordset

set rs = me.recordsetclone

rs.findfirst "[FactNr] = " & [FactNr]

if not rs.nomatch then
    me.recordset.bookmark = rs.bookmark
end if

 

[nanscombe]

Are you trying to achieve something like the attached?

Looks like vbaInet just beat me to it.

 

[vbaInet]

Looks like vbaInet just beat me to it.

Upload it quicker next time

At least now they get to see it in action, so good thing you posted a sample db.

 

[Ciprian]

Thanks guys, you've got it

For some reason I can't make it work on my own db so I've attached it here. If you could modify it, that would be great.

Thanks

 

[vbaInet]

Well Ciprian, what isn't working? What errors do you have?

We're here to advise and (hopefully) help you learn a bit too. Tell us what the problem is and we'll talk through the solution.

 

[Ciprian]

I usually understand code, but the access programing language is still a bit too weird for me. I prefer Excel VBA, C++, mysql

But to answer your question

this is where it get's stuck

.FindFirst "cContractsID = " & Me.ContractNr

That's why asked for help in updating the db i posted. After that i can put the 2 side by side and see where i gone wrong

 

[vbaInet]

Unless we ask for your db, there's no need offering it This a small matter that you can handle with our help.

You mentioned that the contracts id field is Text, so replace that line with this:

rs.findfirst "[FactNr] = " & Chr(34) & [FactNr] & Chr(34)

 

[nanscombe]

It look a bit more complicated than we first thought.

You are trying do a DLookUp where the Invoice could appear in any one of three fields (aiAdvanceInvoiceNumer, fiFinalInvoiceNumber or fiaFinalInvoiceNumber) ... It's not going to work as is.

It'll possibly need a bit of a fiddle ... I'll give it a bit of a think.

 

[nanscombe]

Right ...

Change 1: Query "q nr ctr dupa factura"

Add a column which concatenates all three invoice fields and gives it a name All_Invoices -

All_Invoices: [aiAdvanceInvoiceNumer] & "^" & [fiFinalInvoiceNumber] & "^" & [fiaFinalInvoiceNumber]


Change 2: Form "f Contracte"

Change Control Source of "ContractNr" to

=DLookUp("[cContractsID]","[q nr ctr dupa factura]","All_Invoices like '*" & [FactNr] & "*'")


Change 3: (A bit of VBA code)

The AfterUpdate event of "FactNr" -

Private Sub FactNr_AfterUpdate()
On Error Resume Next

Me.RecordsetClone.FindFirst "cContractsID = '" & Me.ContractNr & "'"
If Not Me.RecordsetClone.NoMatch Then Me.Bookmark = Me.RecordsetClone.Bookmark

End Sub

I've left the code in full this time (by not using With .. End With).


I actually put the Available Invoices listbox there so you could see what Invoices were available in my database.

 

[Ciprian]

Thank you nanscombe and vbaInet for the help with this.