Replacing Dlookup 1

[PALman]

I am trying to write code in VB6 to replace the following VBA code...
PartNo = DLookup("[PartNo]", "EnquiryDesk", "[JobNo]='" & Forms![QAReview]![JobNo] & "'")
This line looks at the field value of the JobNo field in the live/open form/table named "QAReview", finds the same field value for same JobNo field in the unopened/invisible form named "EnquiryDesk" and then returns the field value for the field PartNo for that JobNo from EnquiryDesk table.
I need this code to execute when a chkBox is ticked but understand from previous posts that I need to use a txtBox instead. I have listed the code I have started with but wonder if I am heading in he direction...
General...
Dim Conn As ADODB.Connection
Dim rsJobs As New ADODB.Recordset
Dim rsEnq As New ADODB.Recordset
Load QAReview Form...
Sub Form_Load()
Set Conn = New ADODB.Connection
Conn.Open "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=C:\000-QualityControlProgram\QC5.mdb;Persist Security Info=False"
Set rsJobs = New ADODB.Recordset
rsJobs.CursorType = adOpenForwardOnly
rsJobs.LockType = adLockReadOnly
rsJobs.CursorLocation = adUseClient
rsJobs.Open "Select * From QAContractReview", Conn
End Sub
'And here I am trying to open Enquiry Desk Table...
Set rsEnq = New ADODB.Recordset
rsEnq.CursorType = adOpenForwardOnly
rsEnq.LockType = adLockReadOnly
rsEnq.CursorLocation = adUseClient
rsEnq.Open "Select * From EnquiryDesk", Conn
EnqJobNo = txtFields(9)'<<< txtField(9) is JobNo field in EnquiryDesk table
MsgBox JobNo & EnqJobNo
'The program stops here with error...
"Control array element '9' does not exist"
So any help would be very much appreciated.

 

[Golom]

Try

EnqJobNo = rsEnq.Fields(9).Value

It might also be

EnqJobNo = rsEnq.Fields([red]8[/red]).Value

because the fields collection is zero-based.

 

[PALman]

Golom,
I tried EnqJobNo = rsEnq.Fields(9).Value and it returned "True" then I tried EnqJobNo = rsEnq.Fields(8).Value and it returned "1"
Thanks

 

[Andrzejek]

You may want to, instead of

EnqJobNo = rsEnq.Fields(8).Value

Try

EnqJobNo = rsEnq.Fields("FieldName").Value 
or 
EnqJobNo = rsEnq!FieldName

Where FieldName is the name of your column (field) in your table/recordset.

At least this way you see what field you are going after.

Have fun

---- Andy

 

[PALman]

The JobNo field value that I am looking to have returned is something like 222555 or 222666 etc. which come from the two tables QAReview and EnquiryDesk.
Also, I am not sure about placing this code, assuming I get it to work, behind a textbox. Could there perhaps be an other way.
In Access the code was behind a CheckBox and when this was ticked the code ran ok where the ultimate goal is to create a new Excel document and have certain cells populated by the data found in the EnquiryDesk table against the JobNo key field.
As always any further help is greatly appreciated.

 

[PALman]

Thanks Andy,
That's done it. The JobNo for both tables is now correctly returned equal to each other... 222555 from QAReview table and 222555 from the EnquiryDesk table.
I should now be able to go on and populate my Excel spreadsheet as per previous post which I posted before I seen your solution.
Thanks Andy and Golom

 

[Andrzejek]

I have noticed that you are going after some information in two tables with the same JobNo:

rsJobs.Open "Select * From QAContractReview", Conn
....
rsEnq.Open "Select * From EnquiryDesk", Conn

Why not use just one recordset, let's say recInfo, to collect all info for a JobNo from both tables at once:

rsInfo.Open "Select QAContractReview.*, " _
    & " EnquiryDesk.* " _
    & " From QAContractReview, EnquiryDesk " _
    & " WHERE QAContractReview.JobNo = " _
    & " EnquiryDesk.JobNo " _
    & " AND EnquiryDesk.JobNo = 222555", Conn

Wouldn't that work better for you?

---- Andy

 

[PALman]

Andy
I think you are right. It probably would be better. I shall finish the method I've started with and then try using one recordset. I think though I have another problem...
As I said earlier, I am not sure about placing this code, behind a textbox. Could there perhaps be an other way.
In Access the code was behind a CheckBox and when this was ticked that's when the code executed and my Excel cells were populated with data .
As I am using both a chkBox and a txtBox for the code the code is executed as soon as I enter a record which has this particular chkBox ticked from a previous visit.
Both the chkBox and the txtBox have their Datasource and DataField properties set to the same "LPIreqd" and "datPrimaryRS" respectfully.
The ultimate aim is to create a new Excel document and have certain cells populated by the data found in the EnquiryDesk table against the JobNo key field, but once only, not every time the record is viewed/entered. Also to be able to unclick the chkBox and have the excel document deleted since this unchecked condition means the document is not required. This would allow the user to undo a mistakenly checked box.
Thanks again Andy.



Thanks again

 

[Andrzejek]

It seams to me like guessing game most of the time. As far as my part goes. I like to help, but I don't have a lot of info.

What about this: I have an account on hotmail where you can send your vbp, frm, bas and whatever else you have for this project, along with your Access mdb (if that info is not a top secret, of course). I may then place your mdb in proper location on my HD (if you give the path of where you have it) and then we can go from there. My handle is andrzej7 and it is my junk e-mail place that I get some spam which I clean one a month; and I know tech tips do not recomend this way of help. But this would be a lot better way to see what you have and where you want to go with it.

Let me know what you think about it.

---- Andy

 

[PALman]

Sorry Andy,
Everything I have is for the company for whom I work. While its not top secret the company would not be happy if I sent you this project.
You have helped with various parts of this project, the last being pulldown menus. I have always thanked you and closed of the thread when I acheived my goal.
This new thread is again about transfering an VBA/Access 2000 program which I had running fine, into VB6.
The form has a number of chkBoxes which when ticked opened an Excel template doc filled it with data from two or sometimes three tables then saved this doc with a new name to a Jobs Folder. Here is what I have behind txtBox1 because I cannot use a chkBox1 ,2 3 etc.
Private Sub Text1_Change()
'LPI TICK-BOX
'When LPI box is ticked program sends all known data to LPI Sheet and saves to Job Folder
On Error GoTo Err_Text1_Change

Dim XL As Object, WB As Object
Dim Plant As String, JobNo As String, CertNo As String, SupplierNo As String, Qty As String
Dim PartDesc As String, OrderNo As String, LineItemNo As String
Dim PartNo As String, PartRev As String
Dim EnqJobNo As String

Set XL = CreateObject("Excel.Application")
Set WB = XL.Workbooks.Open("C:\000-QualityControlProgram\WorkingTemplates\LPI.xls")

'If LPIReqd = True Then
If Text1 = -1 Then

JobNo = txtFields(11).Text
WB.Sheets(1).Cells(6, 10) = JobNo

Set rsEnq = New ADODB.Recordset
rsEnq.CursorType = adOpenForwardOnly
rsEnq.LockType = adLockReadOnly
rsEnq.CursorLocation = adUseClient
rsEnq.Open "Select * From EnquiryDesk", Conn

EnqJobNo = rsEnq.Fields("JobNo").Value
'MsgBox "QA Review Job No. = " & JobNo & " Enquiry JobNo = " & EnqJobNo

Qty = rsEnq.Fields("Qty").Value
WB.Sheets(1).Cells(6, 2) = Qty

OrderNo = rsEnq.Fields("OrderNo").Value
LineItemNo = rsEnq.Fields("LineItemNo").Value
WB.Sheets(1).Cells(4, 10) = OrderNo + "/" + LineItemNo

PartDesc = rsEnq.Fields("PartDesc").Value
WB.Sheets(1).Cells(4, 6) = PartDesc

PartRev = rsEnq.Fields("PartRev").Value
WB.Sheets(1).Cells(4, 5) = PartRev

PartNo = rsEnq.Fields("PartNo").Value
WB.Sheets(1).Cells(4, 2) = PartNo

WB.SaveAs ("C:\000-QualityControlProgram\JobsFolder\" + JobNo + "\" + JobNo + "-LPI.xls")
WB.Close

'Else: MPIReqd = False
Else: Text1 = 0

End If

WB.Close
Set WB = Nothing
XL.Quit
Set XL = Nothing

Exit_Text1_Change:
Exit Sub

Err_Text1_Change:
MsgBox Err.Description
Resume Exit_Text1_Change

End Sub

Once again thanks for your help and I am sorry if I have left you guessing.
Thanks

 

[johnwm]

Andy
It's good to see someone being as helpful as you, but please read faq222-2244 to see the posting guidelines. The guidelines on posting emails, and on taking discussion away from the forum are quite clear.
It would be a shame if we were to lose someone as helpful as you!

________________________________________________________________
If you want to get the best response to a question, please check out FAQ222-2244 first.
'If we're supposed to work in Hex, why have we only got A fingers?'
Drive a Steam Roller

 

[Andrzejek]

johnwm
I see your point, and I am aware of the policy. I just thought I would take an 'easy' way to help. Will not happen again.

PALman
Private Sub Text1_Change()
fires every time you Change the context of the text box, which means if you type anything, it will fire after *every* letter you type in the box, including any backspace, etc.

If you want to populate an Excel file, why not a command button "Excel" and have your logic there? When user clicks it - you run your code.

Also, you have:
rsEnq.Open "Select * From EnquiryDesk", Conn
which means you go after ALL records in EnquiryDesk table. Is that what you REALLY want? Don't you want to go after specific record, something like:

rsEnq.Open "Select * From EnquiryDesk WHERE JobNo = " & cboJobNo.Text, Conn

to get all info for this particular Job Number?

In your code you do ask for all info from the table, and since you open rsEnq recordset, you are pointing to the very first record, and you never move to any other record in your rsEnq recordset. Are you aware of this?

Another suggestion: naming conventions

Dim [blue]obj[/blue]XL As Object, [blue]obj[/blue]WB As Object
Dim [blue]str[/blue]Plant As String, [blue]str[/blue]JobNo As String, [blue]str[/blue]CertNo As String, [blue]str[/blue]SupplierNo As String, [blue]str[/blue]Qty As String
Dim [blue]str[/blue]PartDesc As String, [blue]str[/blue]OrderNo As String, [blue]str[/blue]LineItemNo As String
Dim [blue]str[/blue]PartNo As String, [blue]str[/blue]PartRev As String
Dim [blue]str[/blue]EnqJobNo As String

this way, later in your code, you (and all who read it) will know what it is.

HTH

---- Andy

 

[strongm]

>Another suggestion: naming conventions

Opinion is divided on the convention you illustrate. MS and others are moving away from Hungarian Notation (particulalry the somewhat abused version that MS themselves originally adopted after misunderstanding the original paper - from one of their own guys, Charles Simonyi - on the subject).

We have had a thread on the subject, but the search engine can't seem to find it right now ...

 

[PALman]

Hi Andy,
I have tried code behind a command button and it does work best. However I was wondering how user would know if he had clicked on that button. There will be a number of these to allow creation of a number of Excel documents so thats why I started with the chkBox method. Once checked you move on only unchecking (to delete doc) if document was created in error.
Your second point... You are absoultley correct. I just sat down to begin another programming session and I am just about to run with your line...
rsEnq.Open "Select * From EnquiryDesk WHERE JobNo = " & cboJobNo.Text, Conn
And third point... yes I can see the sense in what your saying. This code was from my Access 2000 early days and if I can remember correctly was put together with much help from Tek-Tips friends. So I am just about to change this too.
I shall get back ASAP with results, so thanks again.

 

[Andrzejek]

What you may want to do is dis-able (set Enabled = False) the Excel command button after you run your code. This way your user will not re-run the code for this particular JobNo.

Or, you may just change the Caption of the command button from "Create Excel File" to "Delete Excel File" and have another code to run for the same button with this new caption. This way user will knoe what to do.

You said you have " a number of these [command buttons] to allow creation of a number of Excel documents " - consider using control array of command buttons. It is easy task, just copy and then paste the control, and VB will ask you if you want to create a control array. You will have just one Click event for all of them, and to distinguish between them, use Index parameter:

Private Sub cmdMyCommand_Click(Index As Integer)

Select Case Index
    Case 0
    
    Case 1
    
    Case 2
    
    ...
End Select

End Sub

HTH

--- Andy

 

[PALman]

Thanks Andy,
I like the idea of switching the caption from Create to Delete Excel File and will attempt this shortly.
Also the array method looks to be the ideal way to go when I start to create these other command buttons. However to get there I need a liitle help with line you gave me in your previous post...
rsEnq.Open "Select * From EnquiryDesk WHERE JobNo = " & cboJobNo.Text, Conn
I understand what we are doing but I am not sure about the last part... cboJobNo.Text
The begining of my code now looks like this where I have added msgBox's to keep track of the field values returned...

Private Sub cmdLPIReqd_Click()
'When LPI box is ticked program sends all required field data to LPI Excel Sheet
'and "Saves As" to Jobs Folder
On Error GoTo Err_cmdLPIReqd_Click

'If Text1 = -1 Then
'If cmdLPIReqd = True Then

Dim objXL As Object, objWB As Object
Dim strPlant As String, strJobNo As String, strCertNo As String, strSupplierNo As String, strQty As String
Dim strPartDesc As String, strOrderNo As String, strLineItemNo As String
Dim strPartNo As String, strPartRev As String
Dim strEnqJobNo As String

Set objXL = CreateObject("Excel.Application")
Set objWB = XL.Workbooks.Open("C:\000-QualityControlProgram\WorkingTemplates\LPI.xls")

JobNo = txtFields(11).Text
objWB.Sheets(1).Cells(6, 10) = JobNo

Set rsEnq = New ADODB.Recordset
rsEnq.CursorType = adOpenForwardOnly
rsEnq.LockType = adLockReadOnly
rsEnq.CursorLocation = adUseClient
'rsEnq.Open "Select * From EnquiryDesk", Conn
rsEnq.Open "Select * From EnquiryDesk WHERE JobNo = " & cboJobNo.Text, Conn

EnqJobNo = rsEnq.Fields("JobNo").Value
MsgBox "QA Review Job No. = " & JobNo & " Enquiry JobNo = " & EnqJobNo

strQty = rsEnq.Fields("Qty").Value
MsgBox "Quantity = " & strQty
objWB.Sheets(1).Cells(6, 2) = strQty

The program runs but displays message "object required" prior to any of the msgBox's. If I can see the correct field values for JobNo and QTY (short for Quantity)I shall be able to take it from there.

And once again thanks very much.

 

[Andrzejek]

Here is where ‘guessing in the dark’ comes in.

If I remember correctly, at the very beginning of your program you populate combo box with all Job Numbers from your Access database, right? And then you display very first Job Number in combo box and allow user to chose any Job Number from this combo. I just assumed the name of this combo was cboJobNo and that’s why the Select statement has “ & cboJobNo.Text “ which should give 222555 (or whatever other job number user chooses)

Try:

rsEnq.Open "Select * From EnquiryDesk WHERE JobNo = " & cboJobNo.Text, Conn

Debug.Print "Select * From EnquiryDesk WHERE JobNo = " & cboJobNo.Text

and see what you end up in Immediate window. The Select statement should run in Access SQL builder (if you copy and paste it there)

Also, set some break point(s) in your code and step thru.

---- Andy

 

[PALman]

I am sorry Andy,
There has been a misunderstanding along the way.
I do not populate a combobox with job numbers. Job Numbers appear in txtField boxes in various forms like QAReview.frm and EnquiryDesk.frm and have their DataField properties set to JobNo
Your absolutley correct I have just discovered when trying to retrieve field values from the EnquiryDesk table (while viewing the linking JobNo field in QAReview form) record is not moving from the first record.
So I think I need to change & cboJobNo.Text to connect with the EnquiryDesk table/fields.
I shall get back soon.

 

[PALman]

The line...
rsEnq.Open "Select * From EnquiryDesk WHERE JobNo = " & cboJobNo.Text, Conn
edited to
rsEnq.Open "Select * From EnquiryDesk WHERE JobNo = " & JobNo, Conn
returns the correct JobNo but causes error "Data type mismatch in criteria expression"
So nearly there, just Syntax requires fixing.

 

[Andrzejek]

If your JobNo field in EnquiryDesk table is defined as Char or VarChar - and not a Number - you may want to add quotes:

rsEnq.Open "Select * From EnquiryDesk WHERE JobNo = [red]'[/red]" & JobNo [red]& "'"[/red], Conn

HTH

---- Andy