NO IDEAS...

[Guest]

I have a suppliercode that comes from a tblSupplierCode referenced on the
Product table BUT I have a potential need for a different number to be
entered.
I don't want to overwrite the current code. I want to be able to enter a
different code altogether and have it bound to the order details table which
will eventually be archived.

I thought maybe there was a way to have one control over the other but I
can't figure out any way to do this.

Any ideas or examples I can look at?
Thanks

 

[Joseph Meehan]

I have a suppliercode that comes from a tblSupplierCode referenced on
the Product table BUT I have a potential need for a different number
to be entered.
I don't want to overwrite the current code. I want to be able to
enter a different code altogether and have it bound to the order
details table which will eventually be archived.

I thought maybe there was a way to have one control over the other
but I can't figure out any way to do this.

Any ideas or examples I can look at?
Thanks

Sorry, but it is not clear, at least to me, what the relationship is
between the supplier code and the product. Are you saying one product may
have more than one supplier code. That would not be unusual. You should
have a supplier table and a product table. Now we need to know if a
supplier can have more than one product and a product have more than one
supplier and do you keep records of individual like products (like cartons
of milk or would all cartons of milk come from one supplier?

 

[Guest]

Sorry, but it is not clear, at least to me, what the relationship is

between the supplier code and the product.

I have a qry that links the supplier code to the productID

Are you saying one product may have more than one supplier code. That would not be unusual. You should have a supplier table and a product table.

I do

Now we need to know if a supplier can have more than one product and a product have more than one supplier and do you keep records of individual like products (like cartons of milk or would all cartons of milk come from one supplier?

Yes they can BUT I decided I didn't want to keep all of the codes for all of
the supplier products because I am not in their business so I don't care what
there codes are EXCEPT for the few I already have saved which are the ones we
use the most. So, sometimes I may want to just add a different code to the
Order details tbl which I also have a place for supplier code. I thought
about having a ck box where I could turn the field off but I couldn't get it
to work... it turned off all of the codes for all of the fields when I only
wanted one. Then I tried making it a bound ck box but that still didn't work
right and it didn't remove it from the report.

I guess I could make it a combo or list box and remove the limit to list and
then you could just add another one to the suppliercode tbl... but I am so
tired I can't think any more. I have to think about it tomorrow I guess.
Thanks

 

[John Vinson]

Yes they can BUT I decided I didn't want to keep all of the codes for all of
the supplier products because I am not in their business so I don't care what
there codes are EXCEPT for the few I already have saved which are the ones we
use the most. So, sometimes I may want to just add a different code to the
Order details tbl which I also have a place for supplier code.

I'd suggest using a Combo Box based on your existing supplier-code
table; set the combo's Limit to List property to No. This will let you
pick from the stored set of codes, but also to type in a new code at
will.

John W. Vinson[MVP]

 

[Guest]

ok I tried to just add a combo box. It keeps adding this false code to the
end I don't know where it is coming from. I have tried every combination to
get rid of it but my notinlist code will not work. My suppliercode comes from
the choice made from the cboProductName. My notinlist event is at the bottom.
Thanks for any help! I have other notinlist events and they work fine can't
figure out what is wrong with this one. (By the way if I need to put in the
ProductID as well as the Supplier Code I don't know the syntax...)

SELECT tblSupplierCodes.SupCodeID, tblSupplierCodes.ProductID,
tblSupplierCodes.SupplierCode
FROM tblSupplierCodes
WHERE (((tblSupplierCodes.ProductID)<>False));

Private Sub cboSupplierCode_NotInList(NewData As String, Response As Integer)
On Error GoTo SupplierCode_NotInList_Err
Dim intAnswer As Integer
Dim strSQL As String
intAnswer = MsgBox("The Supplier Code " & Chr(34) & NewData & _
Chr(34) & " is not currently listed." & vbCrLf & _
"Would you like to add it to the list now?" _
, vbQuestion + vbYesNo, "Purchase Order Subform")
If intAnswer = vbYes Then
strSQL = "INSERT INTO tblSupplierCodes ([SupplierCode]) " & _
"VALUES ('" & NewData & "');"
DoCmd.SetWarnings False
DoCmd.RunSQL strSQL
DoCmd.SetWarnings True
MsgBox "The new Supplier Code has been added to the list."
Response = acDataErrAdded
Else
MsgBox "Please choose a Supplier Code from the list." & vbCrLf & _
"Use the ESC to return value to the box."
Response = acDataErrContinue
End If
SupplierCode_NotInList_Exit:
Exit Sub
SupplierCode_NotInList_Err:
MsgBox err.Description, vbCritical, "Error"
Resume SupplierCode_NotInList_Exit
End Sub

 

[Guest]

I just tried the following and it doesn't work either

Private Sub cboProductName_AfterUpdate()
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim strSQL As String

cboProductName.SetFocus
If cboProductName.Value > 0 Then
strSQL = "SELECT * FROM ProductsTTL WHERE ProductID = " &
cboProductName.Value

Set db = CurrentDb
Set rs = db.OpenRecordset(strSQL)
If Not rs.BOF Then

Me.UOM = rs("UOM")
Me.UnitPrice = rs("UnitPrice")

Me!cboSupplierCode.RowSourceType = "Table/Query"
Me!cboSupplierCode.RowSource = strSQL
End If
rs.Close
Set rs = Nothing
db.Close
Set db = Nothing
End If

'Requery the combo
Me!cboSupplierCode.Requery
Me!cboSupplierCode.SetFocus

End Sub

 

[John Vinson]

ok I tried to just add a combo box. It keeps adding this false code to the
end I don't know where it is coming from. I have tried every combination to
get rid of it but my notinlist code will not work. My suppliercode comes from
the choice made from the cboProductName. My notinlist event is at the bottom.
Thanks for any help! I have other notinlist events and they work fine can't
figure out what is wrong with this one. (By the way if I need to put in the
ProductID as well as the Supplier Code I don't know the syntax...)

SELECT tblSupplierCodes.SupCodeID, tblSupplierCodes.ProductID,
tblSupplierCodes.SupplierCode
FROM tblSupplierCodes
WHERE (((tblSupplierCodes.ProductID)<>False));

I'm sorry, but I have NO clue where all this came from, nor what it
has to do with my suggestion - which included *no code at all*, nor
any suggestion to use code.

My suggestion may have been unclear: it was to use a Combo Box to
return the valid SupplierCodes, and to set the Limit to List property
of the combo to No. This setting would PROHIBIT the use of the On Not
In List event, which only fires if you *do* limit the combo entries to
the list; instead, it would allow you to type a free-format,
uncontrolled SubCodeID into the table field if the supplier code was
not in tblSupplierCodes. I would expect that a RowSource like

SELECT DISTINCT tblSupplierCodes.SupCodeID
FROM tblSupplierCodes;

would work... but again *I don't know your database structure*. I may
be blowing smoke here as a result!

John W. Vinson[MVP]