VB programming function

[Bface]

Hello All,

I've been doing some reading to see if I could do this function in my VB programming but it's definitely to advance for me. I have a form whose main source is a query. I am using a Docmd.RunSql INSERT INTO function to insert the record on the form to a table. The record gets inserted into the table after a selection is made from a combo box. The question I have is how do I make the account to no longer be available to the user after it is inserted into the table. It does not matter to me if it is filtered, hidden or if it append to the table.
I've tried to do the append query but it appends the whole query not the individual account. Any input or resources anyone can provide would be greatly appreciated.

[NeoPa]

Do you think you could explain what you're after a little more clearly Bface.
I think there may be an easier way than you're trying but I don't really understand what you're trying to do.

[Bface]

Hi NeoPa,

On my main form I have a combo box which the user has options (Completed, Reviewed, etc.) to select from. Once a selection is made from the combo box the Acct#, PT_name and the selection from Combo box is inserted into a table. In the Combo box AfterUpdate event I entered the code Docmd.RunSql INSERT INTO tblactivity….

After the user have made a selection from the combo box I would like for the account to no longer be available to the user. Is there a way to filter the account from the remaining list. I am not sure if I am using the right term. The idea is if there is a total of 100 accounts for them to go through there should be a way for them to identify which ones they have already looked at. That’s why I was thinking maybe the account can be filtered from the list. I hope this makes sense.

[NeoPa]

BFace,

I'll try to have a look at this a little later (Work - you know :().
In the mean-time, the table MetaData would probably help here.
Post it in Code tags to keep the layout clean.

Table Name=
ID; Autonumber; PK
FieldName1; Numeric
FieldName2; String
etc

[Bface]

Hi NeoPa,

Thanks for taking the time to help me out. I didn't realize you were at work. Ok I have a new idea, since all the account information I need is being transferred to another table then the account can be deleted. As you can tell I am getting desperate. I posted the code below, if you could please let me know what code I can insert to delete the account after it is transferred. Thanks again for all your help.

Code:

Private Sub ComboActivity_AfterUpdate()
 
DoCmd.SetWarnings False
 
mySQL = "INSERT INTO Tbl_AcctWcodes ([Acct#],PT_Name, Acct_Bal, Pt_Status, FC, SC, ComboActivity) Select [forms]![Main Acct Details]![ACCT#] as a, [forms]![Main Acct Details]![PATIENT NAME] as b, [forms]![Main Acct Details]![ACCT BAL] as c, [forms]![Main Acct Details]![S] as d, [forms]![Main Acct Details]![F_C] as e, [forms]![Main Acct Details]![S/C] as f, [forms]![Main Acct Details]![ComboActivity] as g;"
DoCmd.RunSQL mySQL
 
DoCmd.RepaintObject acForm, "Main Acct Details"
Forms![Main Acct Details]!ComboActivity = Null
 
End Sub

[NeoPa]

It can be done.
The basic concept is to link the source data with the table where the newly created records are with a LEFT JOIN and select only those with Null in the latter table in the WHERE clause (or with a filter).
I can't post the code as you haven't posted any MetaData and I don't know what it would be without that.
The answer's here though, just in more general terms.

[NeoPa]

I'm just checking your code now.
Didn't see it till after I'd posted.

[NeoPa]

You don't make it easy. Field names help someone reading the code to understand what is what.
Without any clues as to what data you are (or I am) working with, I can only say :
1. You don't seem to be deleting the original record anywhere.
2. DoCmd.SetWarnings should only ever be used in pairs. Clear to run the code then reset it for normal usage (Or you will learn to regret it).

[Bface]

Hi NeoPa,

As you can see I am new at this. The code I posted is not suppose to delete any record. I was hoping you would tell me what code or command would help me do that after the account was inserted into the table. I have to read allot more on this programming stuff. Thanks for your patience.

[NeoPa]

See post #4.
Without this information I can be little help I'm afraid.