Create new record from combo box choice

[mycotropic]

Hi!
I KNOW that this is very simple somehow;

I have a data entry switchboard that alows users to select an ID using a combo box, then, once they have an ID, select which data they want to enter; choose a form. If the ID in the combo box is already in the table that that form is conected to, the form is opened to that record. If the ID is NOT already in the table it opens to a blank record and they have to type in the ID again.

I am doing goign to the selected record with this code(where 'shelf' is a place that i store the combo boxs' slected value);

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Private Sub cmdgotoclinicallab2_Click()
On Error GoTo Err_cmdgotoclinicallab2_Click
Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "Fclinicallab"

stLinkCriteria = "[VisitNumber]=" & "'" & Me![shelf] & "'"
DoCmd.OpenForm stDocName, , , stLinkCriteria

Exit_cmdgotoclinicallab2_Click:
Exit Sub

Err_cmdgotoclinicallab2_Click:
MsgBox Err.Description
Resume Exit_cmdgotoclinicallab2_Click
End Sub

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
My first qestion is; Is this the most efficient way to do what I am trying to do?
My second question is; How do I get an ID that is selected in the combo box but not in the table that the user selects to be passed there as a new row???? "Man is the cheapest computer we can put into a spacecraft and the only one we can mass-produce with unskilled labor." Werner von Braun

 

[WildHare]

There are various ways you can set this up. There is a "NewRecord" property you can check, and if it is "TRUE" then you can grab the ID from the first form (as long as you haven't closed it).

When the second form opens, check this property. If you are on a new record, you can set the ID as above.

Werner Von Braun's Autobiography:
"I aim for the stars"

Mort Sahl's suggested subtitle:
"But sometimes I hit London"

Remember, you're unique - just like everyone else
You're invited to visit another free Access forum:

http://www.forumco.com/jmhare

or my site,

http://www.jmhare.com

 

[mycotropic]

I wish that I were that smart;
Thank you for your help but could you give me one more step here - what does the VB actualy look like wherin I 'check' that property?

Once I get the logic of this 'check then set' down MOST of my problems are solved!!

I work mainly in SAS you see, where I can IF/THEN adn DO my way through life - VB is NOT very friendly compared to what I'm used to....

Thanks in advance!

g "Man is the cheapest computer we can put into a spacecraft and the only one we can mass-produce with unskilled labor." Werner von Braun

 

[WildHare]

1) Change the call to the form in your switchboard to pass the UserID along as the OpenArgs value:

DoCmd.OpenForm stDocName, , , stLinkCriteria,, Me!{combo-box-name}

2) In the "On Current" event of the form in question, add this one line:

If Me.NewRecord then Me!UserID = me!OpenArgs

CAUTION: If you initiate more than one &quot;add a new record&quot; process while the form is open, the combo-box value will again be set into the field. This is PROBABLY not a good idea. But if you only do ONE <add> at a time, you're OK.





Remember, you're unique - just like everyone else
You're invited to visit another free Access forum:

http://www.forumco.com/jmhare

or my site,

http://www.jmhare.com

 

[mycotropic]

Apparently my stupidity knows no bounds!

In the switchboard the code now looks like this;
stLinkCriteria = &quot;[VisitNumber]=&quot; & &quot;'&quot; & Me![shelf] & &quot;'&quot;
DoCmd.OpenForm stDocName, , , stLinkCriteria, , , Me![shelf]

which compiles so i assume that it is passing the Me![shelf] value

then in the form to which it point i tried various things with several weird results
in the VB editor on the left side at the bottom, i went to the OnCurrent property and changed it to what you indicated which makes sense to me;

If Me.NewRecord Then Me!VisitNumber = Me!OpenArgs

which doesnt DO anything unfortunatly - when i put an id in that is not already in the table it opens the form to a blank and does not add the value.
when i create a Private Sub that looks like this;

Private Sub OnCurrent()
If Me.NewRecord Then Me!VisitNumber = Me!OpenArgs
End Sub

and change the OnCurrent to [event procedure] if wont compile.
when I leave the OnCurrent blank it will compile but it wont do anything - same as above.

I wish I didn't have to work in this language but I do - What am I MISSING here?!?!?!!???

g &quot;Man is the cheapest computer we can put into a spacecraft and the only one we can mass-produce with unskilled labor.&quot; Werner von Braun

 

[WildHare]

Ok, maybe we need to step back just a little.
1) What data source is used to populate your combo box on the first form?

2) What is the relationship between your combo box and the form you hope to open up?

Can you give me a brief outline of your database structure?

This should be as easy as falling off a log, there seems to be one piece that I'm missing here.

If you'd rather, and it's not too large, ship me a working copy of the db. You can sent it to

webmaster@jmhare.com

Don't despair - this should be a snap once I get the full picture.




Remember, you're unique - just like everyone else
You're invited to visit another free Access forum:

http://www.forumco.com/jmhare

or my site,

http://www.jmhare.com

 

[WildHare]

Duhh..my mistake... try this on the On Current guy:


If Me.NewRecord then Me!UserID.VALUE = me!OpenArgs

If you want to see it in action, grab this file:

http://www.jmhare.com/databases/mycotropic.mdb

Remember, you're unique - just like everyone else
You're invited to visit another free Access forum:

http://www.forumco.com/jmhare

or my site,

http://www.jmhare.com

 

[mycotropic]

>1) What data source is used to populate your combo box on >the first form?

A table that contains all of the valid VisitNumbers - there is an invisible text box that contains the value in the combobox since earlyer i was told that i couldnt capture the value selected in the combobox and use that value to open the form that the button points too.
The code that runs the button has been changed from my first post to waht i described above.

>2) What is the relationship between your combo box and the >form you hope to open up?

The value in the combobox will either exist or not exist in the table that is behind the form. If it exists I want to go to that row, if it does not exist I want a new row to open with the value I pass from the combo box by way of the button.
as it is the LinkCriteria is showing up in the Filter box on the form - i assume that this means that, since i am filtering for a record that doesnt exist, i should be calling up the form in some different way......

Thanks very much for your help with this - the monitor is still intact but many more of those fantasticly useless error messages and it might not survive!

g &quot;Man is the cheapest computer we can put into a spacecraft and the only one we can mass-produce with unskilled labor.&quot; Werner von Braun