make a drop down list that the values are less then a value in another table. +conneting two feilds

[milton837]

hello.
i know that this is a very long post, but im realy new to access and i'm trying to learn 'on the go' since i have littlle time to finish this project. so first all thanks for reading!

iv'e spent like 1 hour online trying to figure out the soultion but i'm new to access so i'm guessing i'm searching it all wrong.

i have two tables.

one is an inventory table that contains those feilds:

MAPID(PK),amount,name of the map,amount in borrow.

the second table, contains the people that borrowed the maps and contains:
name of the map,MAPID(FK),amount, and other that is unrelated.

so iv'e got two problems.

1. i'm doing a form to the borrowed maps, and the form contains "name of the map"(combo list which is taken from the inventory table).

i wan't that in the amount textbox(which says how many maps the man want's to take) will be a combo box that is less and equls to the amount i have in the inventory table. for exmple:
if i specify that the man takes USA map, and in the inventory i have 10 of those, so in the amount he will be able to take no more then 10 maps.

2. this is connected to 1, but its kinda diffrent.


i need to establish some connection between sevral feilds in those tables.

i wan't that after i add the record, and lets say the customer took 3 maps of usa, i wan't that the amount of maps i have in the inventory table will be updated (so if i had 10, now it will says 7,cause 3 is on borrow).

another connection i wanna make is whenever i insert the record of the borrowed map, the mapID feild(which is FK), will be updated aswell. i can insert a textbox to do that but i want a more elgent way(since i wan't my form to be as short as it can be).


BTW: if you can direct me to a video that contains the answers to my quiestions that will be lovely aswell.



thanks !

[June7]

1. VBA code in textbox BeforeUpdate event queries for the available total and compares to request

2. inventory balances should be calculated when needed, not saved to table, review http://allenbrowne.com/AppInventory.html

[milton837]

1. VBA code in textbox BeforeUpdate event queries for the available total and compares to request

2. inventory balances should be calculated when needed, not saved to table, review http://allenbrowne.com/AppInventory.html

1. can't seem to find the right syntax for that to happen. always gets an error. can i get more advanced explantion?

2. i'm understanding half of it. maybe a video thats explains it ?
really appreciate the help anyways!!

[June7]

1. post your attempted code, working in a vacuum here, don't know your database

2. don't have any videos

[milton837]

1. post your attempted code, working in a vacuum here, don't know your database
well... i got realy confused trying to solve that.




2. don't have any videos

the text box for one text box is:
mapselect - this is a combo box linked to the inventory table.
and for the text box i want to get a combo box is: amount. - this is where i want the values to be <= to the amount i have in the inventory table.
the inventory table feilds are:
MAPID
map name
amount_in_the_store

i tried something like this:
me.amount.Value<= me.mapselect.Column(2)
but i get a syntax error.

similar code helped do a autofill on other feilds.. so don't know why here its not working..

[June7]

Well, setting the value of a textbox with a <= operator doesn't make sense.

If you want a conditional statement then code in textbox BeforeUpdate event, something like:

If Me.Amount > Me.mapselect.Column(2) Then
MsgBox "Cannot select more maps than available."
Cancel = True
End If

If user could see the available amount as they enter the request, these conflicts might be reduced.

If you want to provide db for analysis, follow instructions at bottom of my post.

[milton837]

Well, setting the value of a textbox with a <= operator doesn't make sense.

If you want a conditional statement then code in textbox BeforeUpdate event, something like:

If Me.Amount > Me.mapselect.Column(2) Then
MsgBox "Cannot select more maps than available."
Cancel = True
End If

If user could see the available amount as they enter the request, these conflicts might be reduced.

If you want to provide db for analysis, follow instructions at bottom of my post.

i realy appreciate the time your spending to help me.

i didn't want a msgbox to pop up, i wanted a combobox that the values that are in the combobox will be depnded on the map that the user has chosen in the form, and the amount i have available in the inventory table.

after 3-4 hours of banging my head at it. i worked out somewhat of a workaround but i'm not comfortable with it at all. it is realy BAD.

i took a new table which consits 1-1000 numbers, and i added an another textbox which is hidden from the user, and set the value of it to the value i have in the inventory table (after the user pickes a map) with me.mapselect.column(2) (just an exmaple).

afterwards,i did a query on the table of numbers so that the critria will be <= [forms]![signform]![texthidden].value.

i do wan't to attach the file.. but the thing is there are alot of things that i typed in hebrew(yea i know its bad,but my first db anyways).

just for example,my sign form name is in hebrew and some other stuff aswell.

do you think you will be able to understand?

once agian, THANKS!!!

[June7]

I have sometimes been able to work my way through a foreign language database. Code is English.

What you described is known as cascading combobox (or listbox). You want a list of numbers for combobox RowSource - number choices limited by amount of available items.

Using a table as datasource of number values for combobox RowSource is a fine solution as long as 1000 will be high enough.
Or code could build a ValueList as RowSourceType. This requires looping code using combobox AddItem method. Something like:

For x = 1 To Me.mapselect.Column(2)
Me.Quantity.AddItem x
Next

Another option is to show user what the limit is and let them do the math to not enter more than available. If they do then slap wrist with message box. They will learn to avoid.

Is this a multiple simultaneous user database? Keep in mind the available count could be inaccurate if multiple users are doing data entry. There will be a delay in updating data display for users.

[milton837]

I have sometimes been able to work my way through a foreign language database. Code is English.

What you described is known as cascading combobox (or listbox). You want a list of numbers for combobox RowSource - number choices limited by amount of available items.

Using a table as datasource of number values for combobox RowSource is a fine solution as long as 1000 will be high enough.
Or code could build a ValueList as RowSourceType. This requires looping code using combobox AddItem method. Something like:

For x = 1 To Me.mapselect.Column(2)
Me.Quantity.AddItem x
Next

Another option is to show user what the limit is and let them do the math to not enter more than available. If they do then slap wrist with message box. They will learn to avoid.

Is this a multiple simultaneous user database? Keep in mind the available count could be inaccurate if multiple users are doing data entry. There will be a delay in updating data display for users.

i heard this name cascading alot. looked it up and all the guides do not envolve vba at all.

i'll take a look at what you said tomorow and will try.

maybe will ask for more help.

thanks agian! you''re a champ.

[June7]

Using a parameterized query object as combobox RowSource possibly does not require VBA (will take your word if you say it works). However, some do not use parameterized query objects (I do not) in which case the parameter is in SQL statement built in combobox RowSource. This does require code to set the RowSource SQL and/or Requery the combobox. There are many examples of this.
https://docs.microsoft.com/en-us/off...mboBox.Requery
http://www.utteraccess.com/wiki/Cascading_Combo_Boxes