completely new to Access 2007

[quest1426]

I am very experienced in using so many different types of software but when it comes to access, all I can do so far is create the table.

Basically I repair computers for a living (not much of one though) and so I have quite a bit of parts for different pc's and laptops.

What I had done in the past is create the table with these columns

item #
Item Description
Quantity
Cost


So now when I purchase more, I manually add the number to my quantity column in the correct field.

As you can tell, this is monotonous, and I have to write down what I use all day and then enter it in manually at night.

I do have a scanner though, and all of the items have the same bar code for the same item. (e.g. dell memory at 1gig ddr has a bar code that is different from another manufacturer, but the same for 100 sticks of dell 1gig ddr.)

So what I want to do is create something for the existing table to use my scanner, scan the number, and add or subtract 1 from the quantity column in the correct field.

So say I scan #542158725, I want it to automatically go to the corresponding quantity and add it, or subtract it if I am using it, by 1.

Is this possible, and if so how the heck can I make my life easier with it.

 

[quest1426]

ah yes, by the way, I don't need fifo simply because that does not matter. I don't use it because I stock pile tons of parts when I find that someone is selling them at whatever price, and I do not let a part sit there for months.

I just need something that I can work with that will use the bar code to either add or subtract.

 

[Mr. B]

First, what you want to do can be done with Access. However, it cannot be done with out creating a form for data entry and writing some VBA code.

The only way to get to where you want to be is to take the projet one step at a time, the same way you would approach fixing my computer.

First, figure out how to create a form that will allow you to enter the data. Take a look at the wizards available in Access.

Once you get the form created, then you can work on trying to get your scanner to work with Access and scan in the bar code. Then comes the VBA code.

Actually, you need to understand that creating what you want is not going to be that simple. It will require the thing I have mentioned but the good news is that there are plenty of folks around here in the forum that will be willing to assist you.

Start with creating your form.

 

[quest1426]

thanks, yes I have created the form a bit and then trashed it like ten times because I don´t know vba.

I did have something from a 2000 example but I am using 2007 and I changed the select [][] from []
and that did work for mine but it stops at the vba code. it tells me that the form works but the increment/decrement does not.

i want to use this quite soon and will post everything in a minute.

 

[quest1426]

okay, I took this example that I found last night and made changes so that the forms are pointing at my table.

In this example I changed it to point at an inventory table and bar code field.

Then the second part I pointed to quantity.

But I do not know vba so I could not make the increment/decrement work.

Can anyone analyze this for me and see what I need to change to make this work in Access 2007?

here is the link for the sample: http://www.taltech.com/downloads/Inventory2000.zip

 

[quest1426]

by the way, the bar code scanner i use automatically works wherever the cursor is.

 

[quest1426]

ah yes, the other thing was that the sample comes from this page with explanation:
http://www.taltech.com/support/dde_sw/ddeaccess5.htm

 

[quest1426]

okay, here is the VBA of the original that tells me function or sub function not defined.


Private Sub cboProduct_AfterUpdate()
On Error GoTo Err_

Dim stDocName As String

'If nothing in the text box, eg, started typing then changed mind and deleted it then quit
If cboProduct.Text = "" Then GoTo Exit_

'check to see if add/edit form is open, if it is then close it
If IsLoaded("frmAddEdit (Manually Add or Edit Table Entries)") = True Then DoCmd.Close acForm, "frmAddEdit (Manually Add or Edit Table Entries)"

'Turn off "You are about to run an update query warning
DoCmd.SetWarnings False

'Run the query
stDocName = "qryChangeStatus"
DoCmd.OpenQuery stDocName, acNormal, acEdit

cboProduct.SelStart = 0
cboProduct.SelLength = Len(cboProduct.Text)

'Save the changes
DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, , acMenuVer70

' Find the record that matches the control.
Dim rs As Object

Set rs = Me.Recordset.Clone
rs.FindFirst "[Product] = '" & Me![cboProduct] & "'"
Me.Bookmark = rs.Bookmark

Exit_:
DoCmd.SetWarnings True
Exit Sub

Err_:
MsgBox Err.Description
Resume Exit_
End Sub

Private Sub frmMode_AfterUpdate()
cboProduct.SetFocus
cboProduct.SelStart = 0
cboProduct.SelLength = Len(cboProduct.Text)
End Sub


so it gives me the error highlighted in yellow here:

Private Sub cboProduct_AfterUpdate()

and then later on here:

Private Sub frmMode_AfterUpdate()

I don't know how to make this work with my 1 table that I have.

It is from the 2000 sample above and I am sure the VBA here can be changed to meet my needs.

 

[Mr. B]

Well, you mention an area highlighed in Yellow. That does not exist here. So I have to make a best guess. If you are getting the "function or sub function not defined" then most likely it is the line:

If IsLoaded("frmAddEdit (Manually Add or Edit Table Entries)") = True Then DoCmd.Close acForm, "frmAddEdit (Manually Add or Edit Table Entries)"

You may not have included the "IsLoaded" funciton when you copied the code from the example. Please understand that I am only guessing.

Here is what I think you are missing:

Public Function IsLoaded(ByVal strFormName As String) As Boolean
 ' Returns True if the specified form is open in Form view or Datasheet view.
    Const conObjStateClosed = 0
    Const conDesignView = 0
    
    If SysCmd(acSysCmdGetObjectState, acForm, strFormName) <> conObjStateClosed Then
        If Forms(strFormName).CurrentView <> conDesignView Then
            IsLoaded = True
        End If
    End If
End Function

Copy and paste this code into a Module. If that is what is causing the problem that that should fix it.

 

[quest1426]

Does anyone know what I need to change if my database has only one table named

INVENTORY

with these columns:

Bar Code

Item Description

Quantity

And that is it. I do not need anymore than this.


oh yeah, the event is on afterupdate

 

[quest1426]

Thanks Mr. B,

This is what I have now


compile error:

expected end sub



Private Sub cboProduct_AfterUpdate()
On Error GoTo Err_

Dim stDocName As String

'If nothing in the text box, eg, started typing then changed mind and deleted it then quit
If cboProduct.Text = "" Then GoTo Exit_

'check to see if add/edit form is open, if it is then close it
If IsLoaded("frmAddEdit (Manually Add or Edit Table Entries)") = True Then DoCmd.Close acForm, "frmAddEdit (Manually Add or Edit Table Entries)"

Public Function IsLoaded(ByVal strFormName As String) As Boolean
' Returns True if the specified form is open in Form view or Datasheet view.
Const conObjStateClosed = 0
Const conDesignView = 0

If SysCmd(acSysCmdGetObjectState, acForm, strFormName) <> conObjStateClosed Then
If Forms(strFormName).CurrentView <> conDesignView Then
IsLoaded = True
End If
End If
End Function

'Turn off "You are about to run an update query warning
DoCmd.SetWarnings False

'Run the query
stDocName = "qryChangeStatus"
DoCmd.OpenQuery stDocName, acNormal, acEdit

cboProduct.SelStart = 0
cboProduct.SelLength = Len(cboProduct.Text)

'Save the changes
DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, , acMenuVer70

' Find the record that matches the control.
Dim rs As Object

Set rs = Me.Recordset.Clone
rs.FindFirst "[Product] = '" & Me![cboProduct] & "'"
Me.Bookmark = rs.Bookmark

Exit_:
DoCmd.SetWarnings True
Exit Function

Err_:
MsgBox Err.Description
Resume Exit_
End Function

Private Sub frmMode_AfterUpdate()
cboProduct.SetFocus
cboProduct.SelStart = 0
cboProduct.SelLength = Len(cboProduct.Text)
End Sub





and finally, the highlighted part is

Private Sub cboProduct_AfterUpdate()

so maybe I just pasted it under what was supposed to be wrong and I did it wrong

 

[Mr. B]

You do have the the IsLoaded function in the wrong place but I think you are attempting to do a whole lot more than you need to be doing.

Check your Private Messages

 

[quest1426]

got it and sent it to you.