Improve on Dlookup

[steve21nj]

I’ve been working with DLookup on a form but wanted to see if I could expand on it further.

When this database idea first came about, we were only processing about 100 bags in our system. We are now processing about 5000 bags. The bags are shipped to us from an outside location with numbers already assigned to the bags. Meaning, there could be duplicate numbers in the bags down the road.

In my table, I have the bag number Indexed with No Duplicates. I would like to change this to allow duplicates. Is it possible to run the code below but have the results fill into a separate form that I could then select the appropriate record based on the results which would populate my original subform that the Dlookup is running After Update?

My attachments:
testform - shows what the original subform looks like.

testoutcome - sample of how i would like the data to populate a new form to then be selected and transfered to the testform. the user would only be allowed to select one record from the list. the data would be sorted by Date In, newest at the top. If no record exist, message saying "no records found".

Option Compare Database
Option Explicit
Private Sub BagNum_AfterUpdate()
'Control Source = Nz(DLookup("Control Source", "my table", "[Control Source after update value] = _
''" & [Control Source after upate value] & "'"))
Me.DateIn = Nz(DLookup("DateIn", "tblPropertyDetails", "[BagNum] = '" & [BagNum] & "'"))
Me.PropertyTypeID = Nz(DLookup("PropertyTypeID", "tblPropertyDetails", "[BagNum] = '" & [BagNum] & "'"))
Me.Notes = Nz(DLookup("Notes", "tblPropertyDetails", "[BagNum] = '" & [BagNum] & "'"))
Me.PropertyStatus = Nz(DLookup("PropertyStatus", "tblPropertyDetails", "[BagNum] = '" & [BagNum] & "'"))
Me.DetentionID = Nz(DLookup("DetentionID", "tblPropertyDetails", "[BagNum] = '" & [BagNum] & "'"))
Me.PropertyID = Nz(DLookup("PropertyID", "tblPropertyDetails", "[BagNum] = '" & [BagNum] & "'"))
Me.Check265 = ""
DoCmd.GoToRecord , , acNewRec
End Sub

 

[billmeye]

You want a temporary landing pad for you to then be able to decide which record is the one you want to use? You then want to select that record and pull in all the pertinent info from that record in order to create your new record that I'm guessing is entered into a different table?

 

[steve21nj]

Thank you for responding.

You want a temporary landing pad for you to then be able to decide which record is the one you want to use?
Answer: Yes. If I could create a form where it would display the results of the BagNumber was = to the search criteria before running the Private Sub BagNum_AfterUpdate()
Even if the results produced one bag, that the (continuous) form would populate. From here I could check a box, click button to run the Private Sub.

You then want to select that record and pull in all the pertinent info from that record in order to create your new record that I'm guessing is entered into a different table?
Answer: Yes. I run the following code after saving the information. It is a dummy table that is deleted on Form_Current

[COLOR=black][FONT=Verdana]Private Sub Command8_Click()[/FONT][/COLOR]
 
[COLOR=black][FONT=Verdana]Dim dbs As Database[/FONT][/COLOR]
[COLOR=black][FONT=Verdana]Set dbs = CurrentDb[/FONT][/COLOR]
 
[COLOR=black][FONT=Verdana]dbs.Execute ("UPDATE tblPropertyDetails INNER JOIN tblPropertyDetailsDummy ON (tblPropertyDetails.PropertyID = tblPropertyDetailsDummy.PropertyID) AND (tblPropertyDetails.BagNum = tblPropertyDetailsDummy.BagNum) SET tblPropertyDetails.DateIn = [tblPropertyDetailsDummy].[DateIn], " _[/FONT][/COLOR]
[COLOR=black][FONT=Verdana]& "tblPropertyDetails.PropertyTypeID = [tblPropertyDetailsDummy].[PropertyTypeID], tblPropertyDetails.DateOut = [tblPropertyDetailsDummy].[DateOut], tblPropertyDetails.PropertyStatus = [tblPropertyDetailsDummy].[PropertyStatus], " _[/FONT][/COLOR]
[COLOR=black][FONT=Verdana]& "tblPropertyDetails.TransferSite = [tblPropertyDetailsDummy].[TransferSite], tblPropertyDetails.TurnoverTo = [tblPropertyDetailsDummy].[TurnoverTo], tblPropertyDetails.DeleteRow = [tblPropertyDetailsDummy].[DeleteRow], " _[/FONT][/COLOR]
[COLOR=black][FONT=Verdana]& "tblPropertyDetails.PropertyManifest_fk = [tblPropertyDetailsDummy].[PropertyManifest_fk] ")[/FONT][/COLOR]
 
[COLOR=black][FONT=Verdana]DoCmd.RunCommand acCmdSaveRecord[/FONT][/COLOR]
[COLOR=black][FONT=Verdana]Me.Refresh[/FONT][/COLOR]
 
[COLOR=black][FONT=Verdana]MsgBox "Records were successfully saved!"[/FONT][/COLOR]
[COLOR=black][FONT=Verdana]DoCmd.Close[/FONT][/COLOR]
[COLOR=black][FONT=Verdana]End Sub[/FONT][/COLOR]

On_Current Event

Private Sub Form_Current()
  Dim dbs As Database
 
  Set dbs = CurrentDb
 
  dbs.Execute ("DELETE * FROM tblPropertyDetailsDummy")
  Me!From.SetFocus
End Sub

 

[billmeye]

My first thought is to add an unbound ComboBox based on the tblPropertyDetails table with the BagNum criteria set to your form field BagNum. In the BagNum After Update you would requery the ComboBox. This will give you your list of previously existing BagNum records. Then in the After Update event of this ComboBox create your new record.

'assign values based on which column contains that field
Me.DateIn = Me.ComboBox.column(1)
Me.PropertyTypeID = Me.ComboBox.column(2)
Me.Notes = Me.ComboBox.column(3)
Me.PropertyStatus = Me.ComboBox.column(4)
Me.DetentionID = Me.ComboBox.column(5)
Me.PropertyID = ""

This will avoid needing to create temp tables, forms etc.
If you need a little assistance and can attach your DB I could help you.

 

[steve21nj]

Thank you for your feedback… Let me attempt to work on your suggestion before posting my DB.

The only issue about it writing to the table immediately is that if a user decides they want to cancel the bag before finishing, the changes have already been made to the table where as the temp table gives them a chance to undo/cancel before saving.

With this form, a user could be updating 50-100 bags at one time, so I am unsure what solution would be the best fit.