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.

 

[PALman]

Thanks Andy,
That change has done it. I am now able to populate all my Excel docs with data dependent on the JobNo on view on the QAReview.frm
I now need to turn my attention on your earlier suggestion to have caption of command button change from Create to Delete. Any guidance you give on this would again be much appreciated.
Thanks again.

 

[Andrzejek]

I see I am getting better in this guessing game. I should try PowerBall.....

Very simple and not very sophisticated way, but it works – I have a cmdExcel with initial Caption of “Create Excel File”

All my logic to Create file is in a Private Sub CreateExcelFile, and all logic to delete it is in another Sub called DeleteExcelFile

Option Explicit

Private Sub cmdExcel_Click()

If Left$(cmdExcel.Caption, 6) = "Create" Then
    Call CreateExcelFile
    cmdExcel.Caption = "Delete Excel File"
Else
    Call DeleteExcelFile
    cmdExcel.Caption = "Create Excel File"
End If

End Sub

Private Sub CreateExcelFile()
[blue]'Place logic here to CREATE file[/blue]

End Sub

Private Sub DeleteExcelFile()
[blue]'KILL Excel file here[/blue]

End Sub

I can see a case of good beer comming my way.....

---- Andy

 

[PALman]

Cheers Andy,
Its 22:00 where I am so will have to leave latest post until tomorrow.
Thanks again.

 

[PALman]

Andy,
I completed the last listing you gave me...

Option Explicit
Private Sub cmdExcel_Click()
If Left$(cmdExcel.Caption, 6) = "Create" Then
Call CreateExcelFile
cmdExcel.Caption = "Delete Excel File"
Else
Call DeleteExcelFile
cmdExcel.Caption = "Create Excel File"
End If
End Sub

Private Sub CreateExcelFile()
'Place logic here to CREATE file
End Sub

Private Sub DeleteExcelFile()
'KILL Excel file here
End Sub

... and it works perfectly. However it only works for the record viewed on the form. When I move to another record the caption remains set to whatever it was when button was clicked.
For example if I create my Excel doc for say record/JobNo 222555 the caption changes to Delete perfectly, but if I move to say record/JobNo 222333 which has not had an Excel doc created, the caption on button still shows Delete.
Is there a way to save the caption status to the active record?

 

[Andrzejek]

...but if I move to say record/JobNo 222333 which has not had an Excel doc created...

In the 'move' portion of your code reset the caption to "Create Excel File"

---- Andy

PS. Would it be out of the question if you would. at least, show me how your form looks like? Just the image.

 

[PALman]

Andy
The moving between records is carried out on the running/active form by clicking the arrows either side of the record navigation bar which was installed at the beginning using the Data Form Wizard. So I am not sure what you mean by "In the 'move' portion of your code "
The project is on my home computer. When I return from work I shall send image of form.
Thanks

 

[Andrzejek]

So, in other words, you have some type of DataControl on your Form that you click First, Next, Last and Previous record. Try Click event of this control to reset your Excel command button Caption (I don't even know if Click is there, I don't use data controls, I do all in code)

HTH

---- Andy

 

[PALman]

Andy,
The DataControl/record navigation bar has two arrows at each end. Clicking the outside arrows shows first and last records. The inside arrows give previous and next records.
The space in the middle displays the Record No.
I cannot find Click event for this. The name of this DataControl is datPrimaryRS. If I was to make another of these controls by drawing in my own from toolbox it would come in named as Data1 data.
Thanks

 

[Andrzejek]

I personaly can not stand this types of controls. If you have 1000 records, and you want to display 500th one, you need to click 500 times no matter where you are - at the First or at the Last record, and a little less clicks if you sit at the 25th one, but still a lot of clicks.

I use combo boxes.

You are on your own with it. Sorry.

---- Andy

 

[PALman]

Point taken Andy.
As I explained I am totally new to VB6 and trying to adapt my Access program is throwing up more problems than I anticipated. I need to be flexible and so will try any method to get up and running. The form you have been helping me with is still being developed. I shall try and see what I can achieve using a combo box but also look at adding a command button to search for any record, which I use succesfully in Access. The code for this amounted to two lines...
Screen.PreviousControl.SetFocus
DoCmd.DoMenuItem acFormBar, acEditMenu, 10, , acMenuVer70
Something tells me it will be more difficult in VB6.
Thanks for all your help. It is much appreciated. I have learned much.

 

[Andrzejek]

I don't have any problems helping you, I acctually like helping people with their VB problems.

Like I said, I do all in code using ADODB. I can never understand bound controls and all that stuff Wizards do for you, and then when you need to change something - nothing works.

Populating combo boxes from DB is easy, users like it, you see it all at once.

For your case, you may try using text box's (txtJobNo?) Change event to reset your Excel command button. Just a guess.

---- Andy

 

[PALman]

Thanks Andy,
I find it hard to understand both wizards and all the help I get from you and the many others here. I shall continue to complete my project and ask many questions on perhaps a new thread. Last comments taken on-board.
Thanks Andy for all you help