CODEWITHSUNDEEP
vbams-access
New2Programming
New2Programming

Reputation: 383

MS Access Forms datasheet to populate all matching records in a dropdown from another table

I am using MS Access 2013 forms.

I have 2 tables: TblA and TblB.

Both have multiple references and values and what I am trying to do is have a datasheet of TblB and have a combobox on the same datasheet which will show the field OrigAmt from TblA that matches TblB.

The purpose of this is because there is no link between the tables other than the reference the user will need to see all values so they can select the value that is in TblA that relates to that record and then TblA will update with values from TblB.

I tried using the lookup field on rowsource from the design where the reference from TblA equals TblB but the datasheet doesn't seem to filter on a row level.

SELECT DISTINCT TblA.Reference, TblA.OriginalAmount
FROM TblA INNER JOIN TblB ON TblA.Reference = TblB.Reference

I'm sure this has probably been answered before but I cant seem to work this out after spending hours on this.

Upvotes: 0

Views: 291

Answers (2)

New2Programming
New2Programming

Reputation: 383

I managed to get this working but it is a bit ugly, I have used 2 subforms within a mainform added a textbox on my mainform to capture what is selected from the subform (TblB) also doing the same for TblA. On the second subform (TblA data) I manually typed the reference into the Link Master/Child fields. Finally I then added an update query to update TblA with TblB data. Hope this can help someone else.

Upvotes: 1

HackSlash
HackSlash

Reputation: 5805

I think I know what you are asking. First, you need to get the selected Reference number to work with. The easiest way to do that is to add a textbox to your form that contains the Reference (Control Source = Reference) and let's name that textbox "CurrentReference". You can hide this textbox if you don't want the user to see it.

Now simply add a where clause to your SQL:

SELECT DISTINCT TblA.Reference, TblA.OriginalAmount
FROM TblA WHERE TblA.Reference = CurrentReference

Now change your combobox Enabled = False so the user can't manually manipulate it. The combobox now only shows the original amount that corresponds to the selected record in TblB.

NOTE: Now that you are displaying only one value, you can use a TextBox and not a ComboBox.

Upvotes: 1

Related Questions