Help Me Fix Text Box To Show Bill No

[aamer]

I have a form on this form have two combo boxes CmbLox & CmbGPNO and a Text Box txtinvo





CmbLox - Here I select the location of Plant

Row Source:

SELECT Sales.SLocation FROM Sales GROUP BY Sales.SLocation;

After Update Event:

Private Sub CmbLox_AfterUpdate()


Dim strSource As String


strSource = "SELECT DISTINCT [CCIDCNo] " & _
"FROM Sales " & _
"WHERE [SLocation] = '" & Me.CmbLox & "' ORDER BY [CCIDCNo]"


Me.CmbGPNO.RowSource = strSource
Me.CmbGPNO = vbNullString




End Sub



CmbGPNO - This Shows The Gate Passes Of The Selected Location.

Row Source:

SELECT Sales.CCIDCNo, HazBillDetail.BillNo FROM Sales INNER JOIN HazBillDetail ON Sales.CCIDCNo=HazBillDetail.GPNo GROUP BY Sales.CCIDCNo, HazBillDetail.BillNo;


Note: It is bound to Column 1 & Column Count is 2


After Update Event:

Private Sub CmbGPNO_AfterUpdate()


Me.txtinvo.Value = Me.CmbGPNO.Column(1)


End Sub



After selection of Location & Gate Pass The Bill No Should Show In Text Box txtinvo
Which It is not showing, where am I making a mistake?

[Bob Fitz]

I'm not sure what difference it might make but I would remove the .Value from:

Me.txtinvo.Value = Me.CmbGPNO.Column(1)

Use:

Me.txtinvo = Me.CmbGPNO.Column(1)

[aamer]

Changed Me.txtinvo.Value = Me.CmbGPNO.Column(1) to Me.txtinvo = Me.CmbGPNO.Column(1)
but no change
still the text box txtinvo is blank and not showing the bill no

[aamer]

Please do note If I change Me.txtinvo = Me.CmbGPNO.Column(0) then the Gate Pass No Shows in text box txtinvo

[Bob Fitz]

Can you post a copy of the db.

[davegri]

You say the rowsource for cmbGPNO is SELECT Sales.CCIDCNo, HazBillDetail.BillNo FROM Sales INNER JOIN HazBillDetail ON Sales.CCIDCNo=HazBillDetail.GPNo GROUP BY Sales.CCIDCNo, HazBillDetail.BillNo;

But that's replaced by the after_update event of the first combobox cmbLOX with "SELECT DISTINCT [CCIDCNo] " & _
"FROM Sales " & _
"WHERE [SLocation] = '" & Me.CmbLox & "' ORDER BY [CCIDCNo]"


So cmbGPNO has only 1 column.

Try this in the after_update event of cmbGPNO

me.txtinvno=Dlookup("BillNo","HazBillDetail","GPNo =" & me.cmbGPNO)


Your design is confusing. It would help to see the DB if you wish to upload it here.

[aamer]

no it did not work either

pls give me some time to empty db and then place it here for your analysis

[aamer]

Hz Ledger 2020.zip Attached is the DB for Analysis

[Bob Fitz]

Setting the Control Source property of "txtinvo" to:

= CmbGPNO.Column(1)

would be all that you need if you kept the Row Source of CmbGPNO as it is shown in design mode BUT it gets changed in the After Update event of "CmbLox".

BTW I have no idea what this db is about but IMHO you tables are in serious need of being normalized. Unless and until that is done I think you will have problems.

Good luck with your project

[orange]

Change this line

Private Sub CmbGPNO_AfterUpdate()
Me.txtinvo = Me.CmbGPNO.Column(0) 'was 1

End Sub

All tables should have PKs.
Field and object names should NOT have embedded spaces NOR special (non alphanumeric) characters.
good luck.

[aamer]

As you said all I have to do is change the source property of txtinvo
= CmbGPNO.Column(1)

still it did not work

[aamer]

Me.txtinvo = Me.CmbGPNO.Column(0)

even this did not work to show bill no. but it does show gate pass no.
may be i did not understand it properly

[Bob Fitz]

As you said all I have to do is change the source property of txtinvo
= CmbGPNO.Column(1)

still it did not work

And if you had bothered to read the rest of my post you may have understood why.

When the form opens the combo box called "CmbGPNO" has two columns but when you make a selection in the combo called "CobTitl" you change the Row Source property of "CmbGPNO" so that it only has one column but I have no idea why that is done or what data you want to display in the textbox.
As I said previously, "IMHO you tables are in serious need of being normalized. Unless and until that is done I think you will have problems."

[orange]

Further to Bob's comments and advice, perhaps you should give an example showing
-what should be selected in the combos
- and what should be the value in the textbox.

Seems we're guessing incorrectly--- to get explicit answer we need explicit instructions.
You know the application and we don't.

[aamer]

If the Plant Location & DC / Gate Pass is selected the bill no should be as high lighted


Example:

Plant Location: Lahore
DC / Gate Pass #: 11204
Bill #: 7

Plant Location: Gujranwala
DC / Gate Pass #: 12901
Bill #: 17


Plant Location: Multan
DC / Gate Pass #: 11793
Bill #: 11