By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
445,704 Members | 1,861 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 445,704 IT Pros & Developers. It's quick & easy.

VB programming function

P: 14
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.
Dec 11 '06 #1
Share this Question
Share on Google+
9 Replies

Expert Mod 15k+
P: 31,656
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.
Dec 11 '06 #2

P: 14
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.
Dec 11 '06 #3

Expert Mod 15k+
P: 31,656

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.
Expand|Select|Wrap|Line Numbers
  1. Table Name=
  2. ID; Autonumber; PK
  3. FieldName1; Numeric
  4. FieldName2; String
  5. etc
Dec 11 '06 #4

P: 14
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.

Expand|Select|Wrap|Line Numbers
  1. Private Sub ComboActivity_AfterUpdate()
  3. DoCmd.SetWarnings False
  5. 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;"
  6. DoCmd.RunSQL mySQL
  8. DoCmd.RepaintObject acForm, "Main Acct Details"
  9. Forms![Main Acct Details]!ComboActivity = Null
  11. End Sub
Dec 12 '06 #5

Expert Mod 15k+
P: 31,656
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.
Dec 12 '06 #6

Expert Mod 15k+
P: 31,656
I'm just checking your code now.
Didn't see it till after I'd posted.
Dec 12 '06 #7

Expert Mod 15k+
P: 31,656
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).
Dec 12 '06 #8

P: 14
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.
Dec 12 '06 #9

Expert Mod 15k+
P: 31,656
See post #4.
Without this information I can be little help I'm afraid.
Dec 12 '06 #10

Post your reply

Sign in to post your reply or Sign up for a free account.