How to create a form with DataGrid??

[colemauro]

How to insert in 2 different tables

Hi

I'm working on a couple projects, and have a similar problem with the two of them.

I have a table with X Products.

I need to create a form that as soon as you enter, shows all the products currently existing in the table Products. The rest i think i can do just find..

Can someone send me a very simple form that does this.

I beleave using a DataGrid is the best way, but if you now something better, please feel free to point it out.

Best regards

 

[georgedwilkinson]

Just drag the table onto a blank form and set the default view to "Datasheet".

 

[colemauro]

Just drag the table onto a blank form and set the default view to "Datasheet".

I was refering to the code part..

Thanks

 

[georgedwilkinson]

Code? You don't need code to display all the records in a table on a form.

 

[colemauro]

Code? You don't need code to display all the records in a table on a form.

Yes i do.. is to manage a stock system.

I have one table with the products, then 2 others, onde for sails and another for purchases.

And i have another named stock and that's were i need the data greed.

It has this:

idProduct
stcPurchase (sum of all stock bought for product A) - 1
stcSail (sum of all stock sold for product A - 2
stcCurrent (1 - 2)

 

[georgedwilkinson]

Can't you just calculate that in the query? No need for code to do that.

If you can't figure out how, provide the names of your tables and fields and your current query's SQL, and somebody here can get you started.

 

[colemauro]

Can't you just calculate that in the query? No need for code to do that.

If you can't figure out how, provide the names of your tables and fields and your current query's SQL, and somebody here can get you started.

I can do a form and put there all the current products and with a few querys do that.

The thing is i don't wanna go that way.. i wanna open a form and i want it to show immediately all the products..as for the calculation of the stocks, i can do that just find..

It's the datagrid thing that brought me here...i never worked with it and i want to and for this case i think it's the best thing..

Thanks

 

[georgedwilkinson]

Okie Doke.

 

[colemauro]

Anyone out there who has worked with this??

I just need a form with all current products without me doing anything..from there on i'll do the rest..

Best regards

 

[colemauro]

How to insert in 2 different tables

I have found a way to go around this

My idea is, everytime i create or update a product in the products form..once i press the button save it will do this:

Private Sub updateStock()
Dim con As Connection
Dim rec As Recordset
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim sql, sql2 As String
Set con = New Connection
Set rec = New Recordset
Set con = CurrentProject.Connection
Set db = CurrentDb()

sql = "SELECT idProduto FROM Stock WHERE idProduto= " & idProduto & ";"
rec.Open sql, con
If rec.Fields(0) = 0 Then
sql2 = "INSERT INTO Stock (idProduto) VALUES ('" & idProduto & "');"
db.Execute (sql2)
End If

The ideia here is to insert a new record in Stock only if it doesn't exist there..if it does exist then he'll do nothing..

The problem is when the sql statement returns nothing.. it gives an error, because it's suppose to have a record there..

I've tried a variaton with only one query

sql = "INSERT INTO Stock (idProduto) VALUES ('" & idProduto & "') WHERE " & idProduto & " not in (Select idProduto FROM Stock) ";

Another error here since you can't do an insert with a WHERE in it a guess..

So what should i do here to fix this ??

 

[colemauro]

Problem solved with this i hope

sql = "SELECT idProduto FROM Stock WHERE idProduto= " & idProduto & ";"
rec.Open sql, con
If rec.EOF Then
sql2 = "INSERT INTO Stock (idProduto) VALUES ('" & idProduto & "');"
db.Execute (sql2)

End if