Solved: Updating Data Multipage 2 to Page 1 Combo box

[Rob342]

Hi
Further to a previous post

I am working on a project for a friend its a multipage user form
when 1 create a code on page 2 then go back to page 1 the data entered does not appear in the combo box
Whats the best way to get the updated data to show without reinitialising the whole form again

Have attached a small copy of the worksheet

Rob

[kpark]

I don't seem to have a problem when running it
How do you recreate the error?

[Bob Phillips]

[VBA]Private Sub CboJCNo_Change()
'// If the Job Code changes then check whether its Add Code or Amend/Delete code
' if i=0 Then the Only Option is To Add
' If i=1 To 999 Then Only Option is to Amend or Delete

Dim i As Integer
i = Me.CboJCNo.listIndex + 1
If i = 0 Then
CommandButton4.Enabled = False
CommandButton5.Enabled = False
CommandButton2.Enabled = True
Me.TxtJCDes.Text = ""
Me.TxtJCStdCharge.Value = 0
Else
CommandButton2.Enabled = False
CommandButton4.Enabled = True
CommandButton5.Enabled = True
End If

Me.CboJCode.Value = Me.CboJCNo.Value

With CboJCNo
If -1 < .listIndex Then
Me.TxtJCDes.Text = Range("JCode").Cells(.listIndex + 1, 2).Value
Me.TxtJCStdCharge.Text = Format(Range("JCode").Cells(.listIndex + 1, 3).Value, "#0.00")
End If
End With
End Sub[/VBA]

[Rob342]

Hi Bob
Thanks for the reply, is this the line you added below
Me.CboJCode.Value = Me.CboJCNo.Value
I can see where you are coming from, but the problem is still the same

When i add a new code on Page 2 & click add it still doesn't update the job code on page 1 without re-initialising the form again.

I thought using a Multipage would be easier but it seems to be more difficult than using a single form for each operation?

Rob

[Bob Phillips]

Don't despair, stick with the MultiPage.

I misunderstood what you meant, this should fix it

[VBA]Private Sub CommandButton2_Click()
'// Add the job code & desription to the list chk for duplicated job code no
Dim i As Integer
Dim IRow As Long
Dim NextNo As Long
Dim CboJCNo As Range
Dim TxtJCDes As String
Dim TxtJCStdCharge As Double
Dim listIndex As Long
Dim ws As Worksheet

Application.ScreenUpdating = False
'i = Me.CboJCNo.listIndex + 1

Set ws = Worksheets("JCodes")
IRow = ws.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row
NextNo = ws.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row - 2

'// Check for Valid Job Code & In sequence
If Me.CboJCNo.Value <> NextNo Then
Me.CboJCNo.SetFocus
MsgBox "Paul, You Must Select a Sequential Number, The Next No is " & NextNo, , "Next Job Code Number "
Exit Sub
End If
'// Check for Text, cannot be blank
If Trim(Me.TxtJCDes.Text) = "" Or Trim(Me.TxtJCDes.Text) = " " Then
Me.TxtJCDes.SetFocus
MsgBox "Paul, The Job Code Text description Cannot be Left Blank", , "Job Code Text"
Exit Sub
End If
'// Check For Job Value Cannot be blank but can be 0
If Me.TxtJCStdCharge.Value = 0 Or Me.TxtJCStdCharge.Value = "" Then
If MsgBox("Paul, Do You Want to leave The Std Charge at Zero ?", vbYesNo) _
= vbYes Then
Else
If Me.TxtJCStdCharge.Value = "" Or IsNumeric(Me.TxtJCStdCharge.Value) = False Then
Me.TxtJCStdCharge.SetFocus
MsgBox "Paul, The Std Charge Value Must be 00.00 Format", , "Std Charge Value"
Exit Sub
End If
End If
End If

'// All the checks done & ok then write the data to DBase sheet
With ws
.Cells(IRow, 1).Value = Me.CboJCNo.Value
.Cells(IRow, 2).Value = Me.TxtJCDes.Value
.Cells(IRow, 3).Value = Me.TxtJCStdCharge.Value
End With
Me.CboJCNo.Value = ""
Me.TxtJCDes.Value = ""
Me.TxtJCStdCharge = ""
'// Reset focus to job Code Numbers
Me.CboJCNo.SetFocus

With ws.Range("JCode").Resize(, 2)
Me.CboJCNo.List = .Value ' p45cal vbax suggestion
Me.CboJCode.List = .Value ' p45cal vbax suggestion
End With
FrmExpress.MultiPage1.Value = 0 'this sets it back to page 1

'Unload Me
'FrmExpress.Show
'UserForm_Initialize
Application.ScreenUpdating = True

End Sub[/VBA]

You will need to make similar changes to CBoJCode Add button if you implement that fully, as well as the Delete button when that comes around.

BTW, your dynamic range names JCode is wrong. You have

=OFFSET(JCodes!$A$3,0,0,COUNTA(JCodes!$A:$A,-1,1))

whereas it should be

=OFFSET(JCodes!$A$3,0,0,COUNTA(JCodes!$A:$A)-2,1)

last question, what does CBoJ stand for?

[Rob342]

Thanks Bob

Still trying to get to grips with the multipage, i think the frustrating bit is not quite understanding the concept.

I'll give it a whirl today and update you later ok
The CboJ.... stands for Combobox Job Code its a pity the form doesn't allow you to have the same name on different pages.

As your a man of the world can you recommend a good excel book that just deals with forms & multipage forms? as the ones that i have only touch the surface and don't really go into detail.

Rob

[Bob Phillips]

Rob,

I agree, you would think that you could qualify the control like MultiPage1.Pages(0).CBoJCode and MultiPage1.Pages(1).CBoJCode as different controls.

I can't say that I do know of any good resources about forms. I am not the biggest fan of forms, you have to do so much work with them, coding functionality that Excel provides intrinsically on a worksheet.

BTW, when I ran your app, I created a new job and left the value as 0. It asked me if I was happy leaving it at 0, I said no, but dkidn't allow me to change it. And it kept calling me Paul

[Rob342]

Hi Bob
Thanks for the info & updates

Have tried the fix and its now updating the Combo box on page0, Superb

There's still a lot of work to do yet, I usually leave the testing & debugging towards the end, then i test it as a user would, inputting rubbish and see how the program reacts.. then go from there.

BTW "Paul" is the friend that this project is for, thought i would personalise it, as its only going to be used by 1 person.

Thanks again for the fix and your time most appreciated.

Rob

[Tinbendr]

its a pity the form doesn't allow you to have the same name on different pages.

I'm perplexed at this setup anyway. Why would you duplicate the combobox for the job number anyway? If job number 1-4 are already on Page one, why do you need them on Page2? (Create a new Job #)

1. I would suggest that you auto increment the job number (from the last job # on the sheet) and display it with a Label so that it not changeable. (Or is there some reason the user needs to pick a particular job number?)

2. You could use a textbox instead and populate (suggest) the NEXT job number in the textbox.

3. Or if you're set on using the combobox, build the list with the next sequence number plus 3-4 more numbers.

[Rob342]

Hi David

The job Code is nothing to do with the job number/invoice number, it is basically a list of Standard Codes for doing various jobs, the set up for creating the codes is on page1 and will now update the combo box on page0.

The job number is a text box that will get updated when i have added another button to create the Job Card, write the details back then update the job number field its currently looking at "MainDB" $o$1.

This is the next one to do on the list & print the job.
If you have any examples of printing to a template Job Card or Invoice that would be great.

Rob

[Tinbendr]

The job Code is nothing to do with the job number/invoice number, it is basically a list of Standard Codes for doing various jobs,

OIC, we call them 'Fault Codes' here.