Inventory Database

[dgarber]

Hello,



I am working on an inventory database and am at a loss on how to get the following to work in a form based on a table named SCAN TABLE.

In my SCAN FORM I have a date field, a lookup field containing employee names of those who will fill the form out, and a field we use to scan a lottery ticket. The barcode on the ticket has 3 bits of information in it which are GAME NUMBER, BOOK NUMBER, and TICKET NUMBER. I created 3 calculated fields titled the same which are automatically populated using LEFT & MID Functions.

Here is where I am stuck. In a separate table named GAMES TABLE I have 4 columns of information which are GAME NUMBER, GAME NAME, TICKET VALUE, and TICKETS IN BOOK. There are about 50 rows with different values such as GAME 54 is $3,000,000 TAXES PAID with a TICKET VALUE of $20 and has 30 TICKETS IN BOOK on ROW 1. What I am trying to do is create 3 more fields on my SCAN FORM named GAME NAME, TICKET VALUE, and TICKETS IN BOOK and populate them automatically.

The population would be based on the scanned GAME NUMBER on the SCAN FORM. It would need to lookup the matching GAME NUMBER in the GAMES TABLE and then populate the GAME NAME, TICKET VALUE, and TICKETS IN BOOK fields on the SCAN FORM from ROW 1 in the GAMES TABLE.

If someone would be so kind as to show me how to do this I would be forever grateful however VBA is not my forte by ant stretch.

Thanks in advance.

Dean

[CJ_London]

first, get your game number datatypes the same. in your scan table it is text, in the games table it is numeric. Numeric is better.

Then learn about normalisation, you should not store data more than once with a very few exceptions - and this isn't one of them.

Instead to view, just create a query, joining the two tables together on the game number and drag down onto the query grid the required fields

However in your form, change the game number textbox to a listbox. The rowsource for the listbox would be the required fields to display from the games table.

Or change the game number textbox to a combobox with the same rowsource as above, and in your ticket value control controlsource reference the relevant column of the game number combo (first column is 0, second is 1 etc) something like

=cboGameNumber.column(1)

[ssanfu]

Also.......

"Date" is a reserved word and a built in function in Access and shouldn't be used as an object name. (in "ScanTable")
Typing in all caps is the equivalent to shouting (see field names and table names). Better would be lower case with first letter capitalized (but NO spaces).

Some suggestions:
Use only letters and numbers (exception is the underscore) for object names.
Do not use spaces, punctuation or special characters in object names.
Do not use look up FIELDS, multi-value fields or calculated fields in tables.
Do not begin object names with a number.

[dgarber]

Ajax,

Thanks for taking the time to reply. I apologize for my newbieness if that is a word?

Question. Are you referring to GAME NAME & GAME NUMBER in both tables concerning normalization? Those 2 would be 2 different bits of information so do I need to change the column name in one table or the other? The GAMES TABLE is an import from the State Lottery and that is how the headers imported.

So just to reiterate, the first 3 digits in the SCAN field on the SCAN FORM represent the GAME NUMBER on that form. From there I need to have the GAME NUMBER on the SCAN FORM query the GAMES TABLE for the matching GAME NUMBER and populate fields on the SCAN FORM with GAME NAME, TICKETS IN BOOK, and TICKET VALUE.

Thanks again!
Dean

[pbaldy]

Post 4 was moderated, I'm posting to trigger email notifications.

[CJ_London]

Question. Are you referring to GAME NAME & GAME NUMBER in both tables concerning normalization? Those 2 would be 2 different bits of information so do I need to change the column name in one table or the other? The GAMES TABLE is an import from the State Lottery and that is how the headers imported.

what you call a field doesn't matter, subject to giving it a meaningful name. You need to develop your own naming convention and ideally follow the rules per post #3, primarily for ease of programming and the avoidance of unnecessary errors. I'm assuming you want to use the value in your scan table to look up the data in your games table - in which case the values should be the same datatype.

As a newbie, follow the advice provided and understand how things work. You will save an awful lot of time compared with thinking you know how it should work

[dgarber]

Ajax,
So I was able to get all of my datatypes changed to numeric. From there I am still at a loss of how to do this. I guess I'm just a step by step person. I've tried several times but not able to get this to work the way we need it to work regarding automatic population in the form fields I mentioned previously. With the most basic explanation I need the GAME NAME, TICKETS IN BOOK, and TICKET VALUE to populate from the GAME NUMBER field on the same form.

[pbaldy]

For a single value I'd use a DLookup(), but for 3 I'd use a recordset. Open a recordset on the table, using the scan in the criteria. Here's some code to get you started:

  Dim strSQL  As String  Dim db      As DAO.Database
  Dim rs      As DAO.Recordset


  Set db = CurrentDb()
  
  strSQL = "SELECT * FROM TableName WHERE FieldName = " & Me.YourScanTextbox
  Set rs = db.OpenRecordset(strSQL, dbOpenDynaset)

  If Not rs.EOF Then
    Me.TextboxName = rs!FieldName
  End If

  rs.Close
  set rs = nothing
  set db = nothing

[CJ_London]

1. on your form, make the control for game number a combo box
2. for the combobox put the following in the rowsource (names taken from your first post so modify if you have changed them)

SELECT [Game Number], [Game Name], [Ticket Value], [Tickets in book] FROM [Games Table] ORDER BY [Game Number]

3. Other properties to change or check in the combobox
Control source:Game Number
Bound Column: 1
Limit to list: yes
Column width: 1;3;0;0 - Edited:
Column Count: 4

I'm assuming you are following good naming practice and have named the combo 'cboGameNumber'

4. in the control source for the Game Name put =cboGameNumber.Column(1)
5. in the control source for the Ticket Value put =cboGameNumber.Column(2)
6. in the control source for the Tickets in book put =cboGameNumber.Column(3)

[dgarber]

Ajax,

In regard to the first line in your follow up post, 1. on your form, make the control for game number a combo box, the game number field is a populated field based on the scanned UPC in the scan field using LEFT Function for the first 3 numbers.

[CJ_London]

Ah, hadn't appreciated that - revert to Pauls suggestion

[dgarber]

Ajax,
So after failing to get this to function the way i wanted, I started over and created a combo box on the form and then created text boxes tied to the combo box for population. All is working fine with the exception of the text boxes are not saving the populated info to the table. Can you offer some help here?

Table is Game Inventory, Column is Game Name
Form is Game Inventory Entry, Text Box is Game Name

[CJ_London]

you don't need to save the data to the scan table - just link in a query when required.

Get out of the habit of looking at tables and use queries instead. Also start to use 'database speak'. Excel has columns, tables have fields.

Also strongly recommend you following the advice of post #3

[dgarber]

Ajax I'm not quite sure how "linking in a query" helps me without the table being populated? The only place the Game Name exists is in the form. I don't necessarily need to populate the table but thats the only way I know to get it into a query? Can you elaborate?