Newbi Access Question

[vj2651]

I'm trying to create an access program for my work (jewelry store). I created on inventory table that has a random number as the primary key. The other fields are item, item number, supplier code, price, and grams. I want to create another form where i can enter the random number to take that item out of inventory. I want that item to then go into another table called the sales table. In the same form where I enter the item number I need to enter a new price because I never get the sticker price. I also need to enter a date into the form. How would i go about creating this form?

 

[PaulBricker]

Well, you don't say whether you want to delete the record out of your inventory table but here's where you can start.
Create your form. Set the Record Source to the Inventory table. Add the fields for Date and Price to the Form and leave them Unbound (you can set up an input mask for the date field and set the Format for the Price field to Currency). In the Form Header, put a Combobox. Set it's Row Source to

SELECT [InventoryTable].[RandomNumber] FROM [InventoryTable];

Then in the AfterUpdate Event for the Combo box put this code:

Me.RecordsetClone.FindFirst "[RandomNumber] = " & Me![ComobName] & ""
Me.Bookmark = Me.RecordsetClone.Bookmark

When you select a random number from the Combo (or you can use a textbox and just type in the random number)the form will move to that record. At this point you can fill in the Date and Price.

Then create a query that has your Inventory Table. Add all the fields you want to go to the Sales table. On the Criteria line for the RandomNumber field put:

=Forms!FormName!ComboboxName

This will filter the query to the record on your form.

On the Menu click Query...Append. This will bring up an Append To box. Type in the name of your Sales table. This should set all the fields in the Append To line equal to the names of your fields in the Sales Table.
To get the Date and Price fields from your Form to append into the Sales table, on the Field Line in your query type in these expressions:

Expr1:Forms!FormName!DateFieldName
Expr2:Forms!FromName!PriceFieldName

and on the Append To line, type in the Date and Price field names in the Sales table.
Then add a button to your Form Header next to the Combobox. In the Click event put this code.

DoCmd.OpenQuery "qryAppendQueryName", acViewNormal

Then when you select the random number (or type it in and hit enter) the record will come up. Then you click the button and it will append the record to the Sales table, including the Date and Price.
If you get this working and want to delete the record from the inventory table, you can create a Delete query and set it up the same way as the Append query and just run it after the append query.
Try it out and post back with specific problems.

Paul

 

[vj2651]

Thanks for the help but what you said sounds French to me but I'm going to see what I can do. Right now I only know the basics of Access but Im learning so I should figure it out just a matter of time. Once again thanks and I will let you know if I encounter any problems.

 

[PaulBricker]

vj, take it one step at a time and post back with questions and problems as you go. It's always easier in the end that it seems at the beginning. That's what this forum is here for.

Paul

 

[vj2651]

I started what you told me and I think I got a good start. I put all the codes and everything in and this is the first error I recieved. Microsoft Access cannot find the macro "me." Also I do want to delete the items from inventory what query do i have to make this possible? thanks

 

[vj2651]

The second error I get is when i press the button. It is
Run time error 7874
Microsoft access cannot find the object 'qryAppendinventoryQuery.'

 

[PaulBricker]

OK, the first part. It sounds to me like you put the code on the line next to the AfterUpdate event. Here's what you need to do.
1. Click on the line next to the AfterUpdate event for the combo box. You should see a little box that has three dots in it ...
2. Click this button and you will have another box appear.
3. Click 'Code Builder' and OK. This will open the AfterUpdate event for the combobox.
4. Inbetween the Private Sub and End Sub lines is where the code goes. It does not go on the line next to the event in the Properties box. That line should have the words (Event Procedure) showing on it.

This should take care of the Macro named Me problem.

The second problem is a little puzzling if you have a query named qryAppendInventoryQuery. Post the code here, and double check the spelling of the query name and the name you inserted in the code.

Paul

 

[vj2651]

Heres the sql code for the query. The file is sort of working but not the way i want.

INSERT INTO sales ( random_numb, item, item_numb, grams, price, [date] )
SELECT inventory.random_numb, inventory.item, inventory.item_numb, inventory.grams, [Forms]![sales]![price] AS Expr1, [Forms]![sales]![date] AS Expr2
FROM inventory INNER JOIN sales ON inventory.random_numb = sales.random_numb
WHERE (((inventory.random_numb)=[Forms]![sales]![combo4]));


Is there any way I can email you the file and you can check whats wrong? I also need to delete the item from inventory. Right now when i click on the append query another box comes up asking me for the price and date and number but it is not connected to the form.

Thanks

 

[vj2651]

Heres the rest of the code:

Option Compare Database

Private Sub Combo4_AfterUpdate()
Me.RecordsetClone.FindFirst "[random_numb] = " & Me![Combo4] & ""
Me.Bookmark = Me.RecordsetClone.Bookmark

End Sub



Private Sub Command6_Click()
DoCmd.OpenQuery "qryAppendinventoryQuery", acViewNormal
End Sub

The DoCmd..... line comes up yellow. I have no idea what that means

 

[PaulBricker]

Send it to pbricker@comcast.net

I'll take a look at it.

Paul