Disappearing Data

[keiath]

On my GIN Form I have subform 'Stock activity' I enter the details but is disappears, but if I look at the stock activity table the data is there, why is it doing that?

[HansV]

Stock Code is a text field, so the On Click event procedure should be

Code: Select all

Private Sub Stock_Code_Click()
    DoCmd.OpenForm FormName:="Stock Control", _
        WhereCondition:="Stock_Code=" & Chr(34) & Me.Stock_Code & Chr(34)
End Sub

[keiath]

Can't seem to get that working at all

[HansV]

I'm not sure what goes wrong, if I add a record to the Stock Activity subform in the database that you sent me, it remains visible...

[keiath]

Odd for me its disappearing but still adding to the table, this happened after I added the calllog subform to the GIN form (which you don't have) seems I've effected something but not sure what at the moment

[Mark L]

On my GIN Form I have subform 'Stock activity' I enter the details but is disappears, but if I look at the stock activity table the data is there, why is it doing that?

Looking at the data in the table, is it correct? Specifically, look at the foreign key field that points to the parent record. I'm guessing this field is not being properly filled.

[keiath]

I have this subform on other forms and shows fine. as I said I pulled another subform onto the GIN form and thats when that data for Stock Activity disappeared.

Still cant see why though

[Mark L]

I have this subform on other forms and shows fine. as I said I pulled another subform onto the GIN form and thats when that data for Stock Activity disappeared.

Still cant see why though

How is the subform supposedto know what data to display? You either have defined the master/child linked fields in the subform properties, or the subform's recordsource contains a WHERE clause that specifies what to look for. Which is it?

[HansV]

The Stock Activity subform6 subform is linked to the GIN form by Reference Number vs GINNO. In the GIN table, however, GINNO is mostly empty - there is only one field that has GINNO filled in. Perhaps you should link on Reference Number vs GINID (which is a calculated text box on the main form)?

[HansV]

PS I see that the field is actually called Refence Number, not Reference Number.

[keiath]

Not sure your understanding the issue, When I enter a stock movement on the subform in my GIN form the data just disappears, but still recreates a record in the stock activity table. So for example the data in the stock control subform 'stock activity' shows and displays that stock movement correctly.

The data is there its just not displaying in the subform of the GIN Form

Hope that makes more sense

[HansV]

I entered some data in the Stock Control subform:

x932.png

When I close the main form and reopen it, the data are still displayed.

I did link the subform to the main form by GINID instead of GINNO, as I suggested, because GINNO is blank.

[keiath]

Right I agree, I tried it that way and if works fine, the only problem is this, The stock activity table is the master audit trial, and is used on any form where stock movement happens. Now of course I only want records from that table to appear in the subform that relates to that form

ie:- If on the Purchase Order form (Tab Stock received) that is actually a stock activity subform. Now if i have an order which has a order number 7 and then I had a Gin id number 7 both stock movements would appear on the subform.

Is there a better way of doing this? As I am sure no matter how far apart different numbers are now they are going to at some point run into each other.

But I am sure you can see why a MASTER stock file is required so it controls the stock control and that the stock control record shows all activity for that item regardless of where it came from. (Purchase orders received, GIN received Stock Trans) and so on

[keiath]

And on that note, If I created addition fields so I have REF field 1 each for purchase, gin, sales order ect then link the subforms to the correct field in the table would that work better? the stockIN and StockOut remians the same...???

On a side note but related, I really want the GIN number id to go, and have GINNO as primary key on auto-numbering but I cant see a way of setting the autonumber at a number you want to start at

[HansV]

I think you'll need someone who has experience with stock control databases to look into this. It's really unfamiliar territory for me, and I don't want to give you incorrect advice.

[keiath]

I'm actually an accountant lol so i have a good deal of experience with this, just not in the 'programming side' but I did try what I said above it works totally fine no over lapping problems by having a ref field for in type of stock movement.

So just need this part sorted:- So On a side note but related, I really want the GIN number id to go, and have GINNO as primary key on auto-numbering but I cant see a way of setting the autonumber at a number you want to start at, is this possible?

[HansV]

You can. When you have an empty table with an AutoNumber field, create an append query that adds one record to the table. Specify a value one lower than the start number that you want for the AutoNumber field. Say you want to start with 1000, then specify 999 as value in the append query. You can leave all other fields blank, unless they are required; in that case specify a dummy value. Run the append query. You now have a dummy record with 999 as AutoNumber value, and the next one will automatically be assigned 1000. You can remove the dummy record.

But if you want some kind of sequence number, it might be better not to use an AutoNumber field, but use code to assign the number. See for example Sequential Numbering on Access MVP ScottGem's blog.

[keiath]

LOL Lost on both them answers!

[HansV]

As it is now, you can't change GINNO in the GIN table to an AutoNumber field, because there is a relationship between GINNO and Refence Number in the Stock Activity table.
Moreover, there are already records in the GIN table.
Would it be OK to remove those records and to start again?

[keiath]

totally all the data is 'dummy' data, only when I put program on server do I copy in live data. If that makes sense.