Combo box and Append query

[JC21]

Hello,
I have build databases before but they were simple ones. I am having difficulties with this one. If someone could assist me it would be greatly appreciated. I have two issues I am not able to solve. My first questions is can a combo box be created to list hyperlinks (particularly to web sites). What I have done is created a table with a list of hyperlinks which goes to different web sites. When I open the table the links works. When I try to add the list to my main form as a combo box the links do not work. From the drop down menu when I try to click on one of the links it does not work.

The second issue I have is I have a main form which contains users account number, name, dept. etc. On this I would like to add a combo box (created from a table) to the main form which gives the users option to select from the drop down list. Once they make the selection from the combo box I would like the user’s acct#, name, and the option they selected from the list to transfer to another table. Once it is transferred I would like for that acct#, name, and dept to no longer be available on the main form. I think it has to be an append query but I have had no luck trying to set thais up. Any help would be greatly appreciated. If what I am asking is crazy please let me know.

Thank you,

JC21

[MMcCarthy]

Hello,
I have build databases before but they were simple ones. I am having difficulties with this one. If someone could assist me it would be greatly appreciated. I have two issues I am not able to solve. My first questions is can a combo box be created to list hyperlinks (particularly to web sites). What I have done is created a table with a list of hyperlinks which goes to different web sites. When I open the table the links works. When I try to add the list to my main form as a combo box the links do not work. From the drop down menu when I try to click on one of the links it does not work.

In the combo box properties under the format tab there is a property at the end of the list called Is Hyperlink. Set this to Yes

The second issue I have is I have a main form which contains users account number, name, dept. etc. On this I would like to add a combo box (created from a table) to the main form which gives the users option to select from the drop down list. Once they make the selection from the combo box I would like the user’s acct#, name, and the option they selected from the list to transfer to another table. Once it is transferred I would like for that acct#, name, and dept to no longer be available on the main form. I think it has to be an append query but I have had no luck trying to set thais up. Any help would be greatly appreciated. If what I am asking is crazy please let me know.

Create a field to put some kind of a trigger on the records available for the form. For example a checkbox field called UnAvail and default it to 0 (for unchecked).

Change the record source for your form to only return records where UnAvail=0.

In the AfterUpdate event of the combo box put the following:

 
Private Sub comboboxname_AfterUpdate()
 
  'assuming all values are available on the form
  DoCmd.RunSQL "INSERT INTO TableName(acct#, name, dept) " & _
      "VALUES (" & Me.[acct#] & ",'" & Me.[name] & "','" & Me.comboboxname & "')"
  'assuming acct# is the primary key i.e. no duplicate values
  DoCmd.RunSQL "UPDATE TableNameOfFormRecordSource SET UnAvail=-1 " & _
      "WHERE [acct#]=" & Me.[acct#] & ";"
  Me.Requery ' this will requery the form
 
End Sub
 
 

[JC21]

First let me start by saying thank you for quick reply. I tried what you mentioned but I think I may be missing a few points. For the Combo box I did change the property for the hyperlink to Yes but it still did not work. For the table I use for the combo box I have 2 fields one is the name of the Comp. (data type: text), the other is the address (Data type hyperlink). I have tried both as the selected field for the combo box but it did not work.
If I am missing something please let me know.


For the second question I have to say I am not that familiar with checkboxes. How do I set the checkbox field to default to 0? For the form the Record Source is a query where it is getting the info. How to I change the record source to only return records where UnAvail=0. The code I have to enter in the AfterUpdate event I understand. Any additional info you can give me I appreciate. Thanks for your time.

[NeoPa]

To set the default value for a checkbox simply open the form in design view; select the checkbox; enter the value required in the 'Default Value' property.
NB. This expects a boolean value - True or False (or Yes; No etc) all work but DON'T require quotes.

[JC21]

Hello,

Thanks for the input you guys give me. I am still not able to get the combo box with the web links to work once an address is selected from the combo box. I did change the Hyperlink property to yes but it did not work. Currently I have a commnad button setup for each web site.

For my main form is there another way I can set it up for the account that is displayed to append to a table after the user makes a selection from a combo box list. I was thinking maybe of creating a one-to-many relationship between the combo box (table) and the main form (query). Once the relationship is created I am not sure what else I will need to do. Any other suggestion I would really appreciated. Thank you all for helping me out.

JC21

[JC21]

Can anyone give me some assistance with problems above. Thanks in advance for any help you can give.

JC21

[MMcCarthy]

Hello,

Thanks for the input you guys give me. I am still not able to get the combo box with the web links to work once an address is selected from the combo box. I did change the Hyperlink property to yes but it did not work. Currently I have a commnad button setup for each web site.

For my main form is there another way I can set it up for the account that is displayed to append to a table after the user makes a selection from a combo box list. I was thinking maybe of creating a one-to-many relationship between the combo box (table) and the main form (query). Once the relationship is created I am not sure what else I will need to do. Any other suggestion I would really appreciated. Thank you all for helping me out.

JC21

Does the hyperlink read

mailto:emailaddress

[JC21]

Hi mmccarthy,

Thanks for helping me with my problems. I was wondering if you or someone could look at this code for me. I received this error when I selected an option from the Combo box.

Error
Run-time error '3134':
Syntax error in INSERT INTO statement

Code:
Private Sub ComboActivity_AfterUpdate()

'assuming all values are available on the form
DoCmd.RunSQL "INSERT INTO Tbl_AcctActivityWcodes (Acct#, Patient Name, Acct Bal, Activity_code) " & _
"VALUES (" & Me.[Acct#] & ",'" & Me.[PATIENT NAME] & "','" & Me.ComboActivity & "')"
'assuming acct# is the primary key i.e. no duplicate values
DoCmd.RunSQL "UPDATE [Qry SP ALL] SET UnAvail=-1 " & _
"WHERE [acct#]=" & Me.[acct_#] & ";"
Me.Requery ' this will requery the form

End Sub

[MMcCarthy]

Firstly when trying to outline code use the delimiters.

code in square brackets at the start and /code in square brackets at the end.

It makes it easier to read...

 
Private Sub ComboActivity_AfterUpdate()
 
'assuming all values are available on the form
DoCmd.RunSQL "INSERT INTO [Tbl_AcctActivityWcodes] (Acct#, Patient Name, Acct Bal, Activity_code) " & _
"VALUES (" & Me.[Acct#] & ",'" & Me.[PATIENT NAME] & "','" & Me.ComboActivity & "');"
 
'>>> You have four fields and only three values. If both the last values are in the combo box what column are they in?
 
'assuming acct# is the primary key i.e. no duplicate values
DoCmd.RunSQL "UPDATE [Qry SP ALL] SET UnAvail=-1 " & _
"WHERE [acct#]=" & Me.[acct_#] & ";"
Me.Requery ' this will requery the form
 
End Sub
 

[JC21]

Hi mmccarthy,

Forgive me but I did not completely understand the question. What I want to do is have the acct#, Pt name, Acct balance and the option selected from the combo box to be transferred to the table. What changes do I need to make in the code for this work?

Thanks,
JC21

[MMcCarthy]

Hi mmccarthy,

Forgive me but I did not completely understand the question. What I want to do is have the acct#, Pt name, Acct balance and the option selected from the combo box to be transferred to the table. What changes do I need to make in the code for this work?

Thanks,
JC21

Leaving the syntax code off for the moment, you have the following
INSERT INTO [Tbl_AcctActivityWcodes]
(Acct#, [Patient Name], [Acct Bal], Activity_code)
VALUES (Me.[Acct#] , Me.[PATIENT NAME], Me.ComboActivity)

So you are trying to put Me.[Acct#] to Acct#. That's fine
Then you are trying to put Me.[Patient Name] to Patient Name. You can't have gaps in field names by the way so enclose these with square brackets which will probably solve your error.

The problem is you now have two more fields:

[Acct Bal] and Activity_code

but in the values list you only have one value Me.ComboActivity. Which one do you want this to go into and where is the other value to come from?

[JC21]

I think I got it now, I forgot to create a value for the [acct bal]. the [activity code] is suppose to go with Me.ComboActivity. Hopefully that makes sense.

Thanks,
JC21

[MMcCarthy]

I think I got it now, I forgot to create a value for the [acct bal]. the [activity code] is suppose to go with Me.ComboActivity. Hopefully that makes sense.

Thanks,
JC21

Yes it does.

Just put square brackets around the field names that have spaces and add a new value for [acct bal] and it should work alright.

[JC21]

Ok I made progress but right before it was going to append the acount I received this error.

Error:
Runtime erro '3073'
Operation must use an updateable query

Code
Private Sub ComboActivity_AfterUpdate()

'assuming all values are available on the form
DoCmd.RunSQL "INSERT INTO Tbl_AcctActivityWcodes([Acct_#],[PT_Name], [Acct_Balance], [Activity_code]) " & _
"VALUES (" & Me.[acct#] & ",'" & Me.[PATIENT_NAME] & "','" & Me.[ACCT_BAL] & "','" & Me.ComboActivity & "')"

'assuming acct# is the primary key i.e. no duplicate values
DoCmd.RunSQL "UPDATE [Qry SP ALL] SET UnAvail=-1 " & _
"WHERE [Acct_#]=" & Me.[acct#] & ";"
Me.Requery ' this will requery the form

End Sub



Is there anyway to bypass the Microsoft Access warning 'You are about to append 1 row(s)'

Thank you,
JC21

[MMcCarthy]

Error:
Runtime erro '3073'
Operation must use an updateable query

The problem this time is with this query. It's not updateable. Can you post the query [Qry SP ALL] in full and I'll have a look at it.

'assuming acct# is the primary key i.e. no duplicate values
DoCmd.RunSQL "UPDATE [Qry SP ALL] SET UnAvail=-1 " & _
"WHERE [Acct_#]=" & Me.[acct#] & ";"

Is there anyway to bypass the Microsoft Access warning 'You are about to append 1 row(s)'

Yes put

DoCmd.SetWarnings False

at the beginning of your code to turn them off and

DoCmd.SetWarnings True

at the end of your code to put them back on.

[JC21]

Here is the SQL code for [Qry SP ALL]. I think this is what you meant by posting it.

code:
SELECT [Tbl PATRIALS].ID, [Tbl PATRIALS].[ACCT#], [Tbl PATRIALS].[MR#], [Tbl PATRIALS].[PATIENT NAME], [Tbl PATRIALS].P, [Tbl PATRIALS].S, [Tbl PATRIALS].[SERV AREA], [Tbl PATRIALS].[ADMIT DATE], [Tbl PATRIALS].[DISCH DATE], [Tbl PATRIALS].[TOTAL CHARGES], [Tbl PATRIALS].[PAT PYMT], [Tbl PATRIALS].[INS PYMT], [Tbl PATRIALS].ADJ, [Tbl PATRIALS].[ACCT BAL], [Tbl PATRIALS].[F/C], [Tbl PATRIALS].[POLICY#], [Tbl PATRIALS].[CAR 1], [Tbl PATRIALS].[CAR 2], [Tbl PATRIALS].[S/C]
FROM [Tbl PATRIALS]
WHERE ((([Tbl PATRIALS].[S/C])="FC"))
ORDER BY [Tbl PATRIALS].[ACCT BAL] DESC;

Sorry for being such a pain.
Thanks again for everything.

[MMcCarthy]

You haven't included Unavil in the query so it can't be updated. Just add it to the query and it should run ok.

Here is the SQL code for [Qry SP ALL]. I think this is what you meant by posting it.

code:
SELECT [Tbl PATRIALS].ID, [Tbl PATRIALS].[ACCT#], [Tbl PATRIALS].[MR#], [Tbl PATRIALS].[PATIENT NAME], [Tbl PATRIALS].P, [Tbl PATRIALS].S, [Tbl PATRIALS].[SERV AREA], [Tbl PATRIALS].[ADMIT DATE], [Tbl PATRIALS].[DISCH DATE], [Tbl PATRIALS].[TOTAL CHARGES], [Tbl PATRIALS].[PAT PYMT], [Tbl PATRIALS].[INS PYMT], [Tbl PATRIALS].ADJ, [Tbl PATRIALS].[ACCT BAL], [Tbl PATRIALS].[F/C], [Tbl PATRIALS].[POLICY#], [Tbl PATRIALS].[CAR 1], [Tbl PATRIALS].[CAR 2], [Tbl PATRIALS].[S/C]
FROM [Tbl PATRIALS]
WHERE ((([Tbl PATRIALS].[S/C])="FC"))
ORDER BY [Tbl PATRIALS].[ACCT BAL] DESC;

Sorry for being such a pain.
Thanks again for everything.

[JC21]

I’m not sure how to add UnAvail to the query since [Qry SP ALL] is getting all its fields from [Tbl PATRIALS]. Should it be included in the table? I am lost on this one. Please let me know how to add it to the query.

Thanks!

[MMcCarthy]

I’m not sure how to add UnAvail to the query since [Qry SP ALL] is getting all its fields from [Tbl PATRIALS]. Should it be included in the table? I am lost on this one. Please let me know how to add it to the query.

Thanks!

UnAvail should be included in [Tbl PATRIALS] if you want to use it as an indicator. Just add it as a True/False type field.

[JC21]

Thank you for all your help mmccarthy. Sorry I took so long to reply back.

[MMcCarthy]

Thank you for all your help mmccarthy. Sorry I took so long to reply back.

No problem.

I'm glad you got it working.

Mary