Insert data into table from global variable 1

[skyline666]

Hi,

Im using Access 2003 Project and SQL Server 2005. I have a connection screen which lets the user pick a platform from a combo box and then all projects for that platform are displayed in a list box. The user then chooses that project and presses a command button which opens up a new form called RiskEstimating (RE). The Platform and Project are stored in 2 global variables (intPlatform and intProject).

On this form are a lot of option buttons, with 3 option buttons to a group (being can only choose one option in that group). The value for these are stored in the RE table. PlatformID and ProjectID are two other fields in this table. There is also a primary key field called RiskEstimateID which is an autonumber field. Also there are two text boxes aswell that visibly, at the moment, display the global variables.

I have made some dummy data (4 records in total) so that I could open up the RE form and display only the RE's for the Platform/Project chosen. At the moment when I open up the RE form for Platform/Project, the platformID and projectID display, but they don't display if there isn't an estimate for it (obviously). When the user is done, he/she clicks on a command button (cmbClose) that closes that form and returns to the connection screen.

What I would like to do is on the OnClick event of this close button, input the platform/project selected into the two fields if it is a new record, and if it isn't a new record (ie: the user is looking at an existing estimate), then just close as normal as don't need to store the platform/project because it is already there.

Many thanks

Andrew

 

[Remou]

how about an update query and the NewRecord property?

 

[skyline666]

Ive never done an update query before and had a quick look around on it and come up with something that im not sure is right, could you take a quick look?

create procedure [dbo].[usp_UpdateRE]
@PlatformID int,
@ProjectID int

as
begin

UPDATE RiskEstimating

set PlatformID = @PlatformID,
    ProjectID = @ProjectID

end

Also how ould I do it via the NewRecord property?

Andrew

 

[Remou]

I have checked a little further and my suggestion was so good.

I think the best bet would be to update the two controls in the After Update event of the Option Group:

If Me.NewRecord Then
  Me.txtProjectID = intProject
  Me.txtPlatformID = intPlatform
End If

 

[skyline666]

That did it.

Thanks Remou