Multiple Concatinations to Equal a single Location

[Nwtech75]

Greetings all,
My name is Vince. I'm new to the community. I have a beginner to intermediate knowledge of VBA and Access. I am working Access 2007-2010, and seem to have hit a bit of a knowledge block. I'm still learning so please be as detailed as you can in your explanations. I will be as detailed as I can in my description.

I am working with an unbound form to control when and how records are added to the table. I have included an image of the form that I have created. This is just a functional layout, I'll clean it up and finalize locations later, right now I just want the form to accomplish the goals. I you look at the form, the label "Location" is actually a 6 part input. In the Code they are named and listed sequentially from LOC to SLOT. Because of the way we organize our parts, this was actually the simplest way to do it. While working on making sure the form would do what I wanted it to, it occurred to me that I needed to prevent more than one part from being installed in the same location. What I am attempting to do is to run a check for records in the table that already contain the exact combination of all 6 inputs. WHERE LOC, SORT, SID, FILE, PART & SLOT all are already listed in the table. Now I know that this is most likely some sort of concatenation, but I honestly have absolutely no idea where to start the Dim's, or how to do the check. I thought maybe a DCount might do the trick, but I don't know how to measure the response. Is it possible to utilized two concatenated statements in a single DCount? I know the check will have to come before the .AddNew and I will have to build in an If Statement for any result that is >0. Perhaps if someone could help me figure out how to concatenate all 6 inputs into a single request string and do the same for the table output I might be able to go the rest of the way on my own.

I'm still learning, but the books are not exactly straight forward in many cases. Does anyone have any idea's?


18162



Private Sub cmd_ADD_Click()

Dim dbsFreeStock As DAO.Database
Set dbsFreeStock = CurrentDb
Dim rstParts As DAO.Recordset
Set rstParts = dbsFreeStock.OpenRecordset("K_Parts") 'Need to try and change the K to H based on the input value of LOC

'Check for duplicate locations to prevent adding more than one part to the same container. Write If statement to handle.

rstParts.AddNew
rstParts!CPN = Me.txt_CPN.Value
rstParts!MPN = Me.txt_MPN.Value
rstParts!DESC = Me.txt_DESC.Value
rstParts!NOTE = Me.mem_NOTE.Value
rstParts!LOC = Me.cmb_LOC.Value
rstParts!SORT = Me.cmb_SORT.Value
rstParts!SID = Me.txt_SID.Value
rstParts!FILE = Me.txt_FILE.Value
rstParts!PART = Me.cmb_PART.Value
rstParts!SLOT = Me.txt_SLOT.Value
rstParts!COST = Me.txt_COST.Value
rstParts!MIN_QTY = Me.txt_MIN_QTY.Value
rstParts!REF_QTY = Me.txt_REF_QTY.Value
rstParts.Update

'Clear the form and start from the beginning

Me.txt_CPN = Null
Me.txt_MPN = Null
Me.txt_DESC = Null
Me.mem_NOTE = Null
Me.cmb_LOC = Null
Me.cmb_SORT = Null
Me.txt_SID = Null
Me.txt_FILE = Null
Me.cmb_PART = Null
Me.txt_SLOT = Null
Me.txt_COST = Null
Me.txt_MIN_QTY = Null
Me.txt_REF_QTY = Null
Me.txt_CPN.SetFocus

End Sub


Many thanks in advance for any input you can provide

[jonh]

Use SQL to get and update records.

Get the record matching LOC, SORT, SID, FILE, PART & SLOT. If no record exists .eof is true.


set rs = currentdb.openrecordset("select * from K_Parts where LOC='" & me!cmb_LOC & "' and SORT='" & me!cmb_SORT & "' and SID='" & me!txt_sid & "' and FILE='" & me!txt_file & "' and PART='" & me!cmb_part & "' and SLOT='" & me!txt_slot & "'")


if not rs.eof then
currentdb.execute "insert into K_Parts (cpn,mpn,desc,note,LOC, SORT, SID, FILE, PART , SLOT .... ) values ( .... '" _
& me!cmb_loc & "','" & me!cmb_sort & "','" & me!txt_sid & "','" & me!txt_file & "','" & me!cmb_part & "','" & me!txt_slot & "' .... )"
end if




A better way would be make LOC, SORT, SID, FILE, PART & SLOT a multi field primary key in specific table tblLocation and use their ID in any other tables.
Then you can just use the above insert without the check and just catch any errors raised.

[Nwtech75]

Jonh, Thanks for the suggestion. This actually looks a lot simpler than what I am currently utilizing.


Private Sub txt_SLOT_LostFocus()


'Dim dbsFreeStock As DAO.Database
'Set dbsFreeStock = CurrentDb


'Dim Duplicates As DAO.Recordset
'Set Duplicates = dbsFreeStock.OpenRecordset("qry_DupLocation")


Dim ZONE As Variant
ZONE = Me.cmb_ZONE
Dim SORT As Variant
SORT = Me.cmb_SORT
Dim SID As Variant
SID = Me.txt_SID
Dim FILE As Variant
FILE = Me.txt_FILENBR
Dim PARTITION As Variant
PARTITION = Me.cmb_PARTITION
Dim SLOT As Variant
SLOT = Me.txt_SLOT
Dim Location As String
Location = ZONE & "-" & SORT & SID & "-" & FILE & "-" & PARTITION & SLOT


Me.txt_result = Location 'Hidden Field


Me.txt_count = DCount("[ID]", "tbl_Parts", "LOCATION =" & "'" & Location & "'") 'Hidden field that checks for duplicate Locations


If Me.txt_count.Value > 0 Then 'Hidden Field
Canel = True
If MsgBox("You are attempting to enter a Part in an occupied location. Check desired location and try again.", vbOKOnly, "Error Duplicate Entry") = vbOK Then
Me.cmb_ZONE = Null
Me.cmb_SORT = Null
Me.txt_SID = Null
Me.txt_FILENBR = Null
Me.cmb_PARTITION = Null
Me.txt_SLOT = Null
Me.txt_result = Null
Me.cmb_ZONE.SetFocus
End If
Else
Me.txt_MINQTY.SetFocus
End If


End Sub


While this is effective and does accomplish the goal, I will try your code in my back up model and see how things go.

Thanks again.